Aggregate function - Logical dimension table

Question

Hello Experts,

I have a scenario where I had to use MAX function in logical dimension table to retrieve zip code at each stop.

My data is like below

Route         Stop Number      Zip Code

Bos_to_NY         1              25897

Bos_to_NY         2              84578

Bos_to_NY         3              34421

Now I am creating new logical columns for each stop with below logic and I am using MAX to avoid NULLS.

MAX(CASE WHEN STOP NUMBER =1 THEN ZIP CODE ELSE NULL END) AS STOP 1 ZIP

MAX(CASE WHEN STOP NUMBER =2 THEN ZIP CODE ELSE NULL END) AS STOP 2 ZIP

Now the issue is that, BI server is generating query such that MAX value of all routes is retrieved and then showed across all routes.

It is using OVER () and other parition by clauses and not GROUP BY to get the results.

Surprisingly if I put the same above logic in Answers 'Edit Formula', I get the right results.

Can anyone let me know what I am missing here?

Thanks

Robert Angel · Answer

Did we resolve your issue if so can you kindly close the question.

Robert Angel · Answer

Last, but by no means least, if you can do this in the ETL then do.

Robert Angel · Answer

Btw - I would do this in more than one measure, you don't have to expose them all to the user.

For the case logic make it a physical calculation.

For the max logic base it on the physical case logic calculation.

Pin as previously noted to the detail level of route.