Forum Discussion
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"))
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 🙂
4 Replies
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 🙂
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)
)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 🙂
