topic Re: RANKX Expression for 3 Columns in DAX Commands and Tips

RANKX Expression for 3 Columns

Matt1979 — Wed, 07 Jun 2023 23:27:50 GMT

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:

ID	Attribute	Value
111	A	60
111	B	50
112	A	50
112	B	70
113	A	20
113	B	40

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!

Re: RANKX Expression for 3 Columns

Jihwan_Kim — Thu, 08 Jun 2023 03:29:45 GMT

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

Re: RANKX Expression for 3 Columns

Matt1979 — Fri, 09 Jun 2023 13:23:57 GMT

Thanks! That works...