Aggregate function - Logical dimension table

User_YQZHD

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

Martin van Donselaar

What exactly are you trying to achieve? It seems like a cumbersome manner to pivot your results.

Please provide a detailed example of the expected results, and how you plan to use those results (why is this needed)

Robert Angel

Do you have a dimension hierarchy associated with Route?

If so try pinning the max measure to the detail level.

Robert Angel

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.

Robert Angel

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

Robert Angel

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