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.
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.
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" )
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!