Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"))
Solved! Go to Solution.
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |