Filter Value then Rank them by descending order

[ngiam]

Hi All,

I got a table in power bi with the following structure

Name | %C | %A | %T | %U |

Paul | 60% | 56% | 34% | 75%|

How do I get power bi using DAX to read the % and if any % in the | %C | %A | %T | %U | is above 50%, it will capture the alphabet according to the descending value of the %. For example, for Paul, the column next to him should read UCA

Thank you in advance!

[ngiam · Accepted solution]

Closing the loop here. Manage to find the answer eventually:

UCA_Column =

VAR CValue = IF ( table1[C3%] >= 0.5, 1, 0 )

VAR UValue = IF ( table1[U3%] >= 0.5, 1, 0 )

VAR TValue = IF ( table1[T3%] >= 0.5, 1, 0 )

VAR AValue = IF ( table1[A3%] >= 0.5, 1, 0 )

VAR AttributeTable = FILTER (

        UNION(

            ROW("Attribute", "C", "Percentage", table1[C3%] * 100, "Include", CValue),

            ROW("Attribute", "U", "Percentage", table1[U3%] * 100, "Include", UValue),

            ROW("Attribute", "T", "Percentage", table1[T3%] * 100, "Include", TValue),

            ROW("Attribute", "A", "Percentage", table1[A3%] * 100, "Include", AValue)

        ),

        [Include] = 1

    )

VAR RankTable = ADDCOLUMNS(AttributeTable, "Rank", RANKX(AttributeTable, [Percentage], , ASC, Dense))

RETURN

    CONCATENATEX(RankTable, [Attribute], "", [Percentage], DESC)

[Sahir_Maharaj]

Hello @ngiam,

Can you please try this: 

UCA Column = 
VAR SortedTable =
    SORT (
        ADDCOLUMNS (
            FILTER (
                ALL ( Table1 ),
                Table1[Name] = SELECTEDVALUE ( Table1[Name] )
            ),
            "Attribute", SWITCH (
                TRUE (),
                Table1[%C] >= 0.5, "%C",
                Table1[%A] >= 0.5, "%A",
                Table1[%T] >= 0.5, "%T",
                Table1[%U] >= 0.5, "%U"
            )
        ),
        [Attribute], DESC
    )
RETURN
    CONCATENATEX ( SortedTable, SortedTable[Attribute], "" )

[ngiam]

Hi @Sahir_Maharaj ,

Thank you for your support. I'm getting this error code: Failed to resolve name 'SORT'. It is not a valid table, variable, or function name.

Is there something that I should do from my end?

Thanks