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