Forum Discussion

Matt1979's avatar
Matt1979
Frequent Visitor
3 years ago
Solved

RANKX Expression for 3 Columns

Hi everyone, first post.  Using the sample data below (call the table RANKX Test), I want to write a RANKX measure that ranks the Attribute column by the Value column, for each ID column value:

 

IDAttributeValue
111A60
111B50
112A50
112B70
113A20
113B40

 

I know how to rank attribute by value, and create a visual level filter for the top ranked attribute:

 

Rank Attributes = IF(RANKX(ALLSELECTED('RANKX Test'[Attribute]),CALCULATE(SUM('RANKX Test'[Value])),,ASC,Dense)

                                 <=1,1,0)

 

A table with the Attribute column and a column with the COUNT of Attribute values looks like this:

 

Attribute         Count of Attribute

A                     3

 

I need to add the ID column to the measure, so that the attributes are ranked for each ID.  I would want the table above to look like this since A would be the top ranked attribute for two IDs and B would be the top ranked attribute for one ID:

 

Attribute             Count of Attribute

A                         2

B                         1

 

Appreciate any help on this!

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

     

     

     

    Count of attribute expected result: =
VAR _currentrowattribute =
    MAX ( Data[Attribute] )
RETURN
    IF (
        HASONEVALUE ( Data[Attribute] ),
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    ALL ( Data ),
                    "@Rank_partion_ID",
                        RANK (
                            skip,
                            ALL ( Data ),
                            ORDERBY ( Data[Value], ASC ),
                            PARTITIONBY ( Data[ID] )
                        )
                ),
                Data[Attribute] = _currentrowattribute
                    && [@Rank_partion_ID] = 1
            )
        )
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

     

     

     

    Count of attribute expected result: =
VAR _currentrowattribute =
    MAX ( Data[Attribute] )
RETURN
    IF (
        HASONEVALUE ( Data[Attribute] ),
        COUNTROWS (
            FILTER (
                ADDCOLUMNS (
                    ALL ( Data ),
                    "@Rank_partion_ID",
                        RANK (
                            skip,
                            ALL ( Data ),
                            ORDERBY ( Data[Value], ASC ),
                            PARTITIONBY ( Data[ID] )
                        )
                ),
                Data[Attribute] = _currentrowattribute
                    && [@Rank_partion_ID] = 1
            )
        )
    )