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.
I have a dataset with various numerical value as text. I like to create two custom Dax columns that (1) identifies the top 3 values, but is dependent on the text values in a second column, and (2) create another dax column to describe the results. The example below shows the desired result of the 2 custom dax columns. Here the data is filtered for category A (in Column 2).
Column 1 Column 2 Custom Dax Column 1 Customer Dax Column 2
10 A 10 First Top Value
9 B null Remaining Value
3 A 3 Third Top Value
6 A 6 Second Top Value
8 C null Remaining Value
Any and all help appreciated. Thanks
Solved! Go to Solution.
@tod -
Did you try:
Proud to be a Super User!
Thanks very much for taking the time to respond and providing the solution!!
I used it in my Power BI desktop report and for some reason, I'm not getting the same outcome. The data is filtered only on A. Therefore, remaining values are not showing. Not sure what I'm doing wrong in Power BI desktop.
@tod -
Did you try:
Proud to be a Super User!
Thanks! in my Dax Column 1, the data was summarized. Selecting "Don't Summarize" displayed the other categories.
@tod -
You can do as:
Custom DAX Column 1 =
IF(
TableName[Column2] = "A",
VALUE(TableName[Column1]), BLANK()
)
Custom DAX Column 2 =
VAR calc =
IF (
ISNUMBER ( TableName[Custom DAX Column 1] ),
RANKX (
TableName,
TableName[Custom DAX Column 1],
TableName[Custom DAX Column 1]
),
BLANK ()
)
RETURN
SWITCH (
TRUE (),
calc = 1, "First Top Value",
calc = 2, "Second Top Value",
calc = 3, "Third Top Value",
"Remaining Value"
)
Proud to be a Super User!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |