cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Case Statement with Over Partition By

Hello @amitchandak ,

 

I would need your help again.

As per your guidance, I have created the DAX calculation as below. But I see wrong results meaning it shows the maximum for each record (COLUMNB), instead of group of fields (COLUMNC and COLUMND). Below is the SQL Vs DAX. Kindly help.

 

SQL:

SELECT CASE WHEN COLUMN A = 'A' THEN
Max(COLUMN B) OVER ( partition BY COLUMN C, COLUMN D) END AS Cal
FROM TABLE
WHERE COLUMN A IN ( 'A', 'B' )

 

DAX:

calculate(max(COLUMN B),allexcept(TABLE, COLUMN C,COLUMN D),filter(TABLE,COLUMN A="A"))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Case Statement with Over Partition By

@BSM1985 

 

The formula  that I shared can be used as a measure. If you need to see zero if the result is a blank then just add +0 at end to my formula. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Case Statement with Over Partition By

@BSM1985 

Can you this Measure?

Measure = 
calculate(
    max(COLUMN B),
    COLUMN A="A",
    allexcept(TABLE, COLUMN A, COLUMN C,COLUMN D)
    
)

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Highlighted
Helper IV
Helper IV

Re: Case Statement with Over Partition By

Hello @Fowmy 

 

Thanks for taking your time to look into it.

I'm almost there, I would like to see the 0s if condition does not match COLUMN=A. I added the screesnhot below. Please refer CALCULATED MEASURE field in the below screenshot.

 

Request: can this be done thru calculated measure becasue if I use calculated column I see the message 'Out of memory..' due to the size of the model?

 

DAX:

CALCULATED MEASURE =
calculate(
max(COLUMN B),
COLUMN A="A",
allexcept(TABLE, COLUMN A, COLUMN C,COLUMN D)
)

 

0s Output.PNG

Highlighted
Community Champion
Community Champion

Re: Case Statement with Over Partition By

@BSM1985 

 

The formula  that I shared can be used as a measure. If you need to see zero if the result is a blank then just add +0 at end to my formula. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

View solution in original post

Highlighted
Helper IV
Helper IV

Re: Case Statement with Over Partition By

Thanks much @Fowmy 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors