Forum Discussion

tod's avatar
tod
Frequent Visitor
5 years ago
Solved

Help with creating custom dax columns

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

Need Help

4 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    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"
    )

  • tod's avatar
    tod
    Frequent Visitor
    5 years ago

    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's avatar
        tod
        Frequent Visitor
        5 years ago

        Thanks! in my Dax Column 1, the data was summarized. Selecting "Don't Summarize" displayed the other categories.