Rankx but exclude some column values from ranking

Regular Visitor

 

I'm new to Power Pivot and I have been using DAX for about a week. I have been trying to figure out how to use Rankx but so it returns blank for two rows and then ranks everything else in the column.

1. I think I am almost there with the formula below. Is there a shorter/better way of doing this? Most  of the examples that I saw online used SELECTEDVALUE but I think that only applies to power BI because I don't see it on my function list. Which is why I ended up using AND and hasonevalue, etc.

2.  I want it to rank over everything except the two values "Retirement" and "Other" but it is not currently doing that. How specifically should I edit my formula so it does do that.

3. Note I am using all sections: Filters, Columns, Rows, Values. Whatever solution is provided should keep this in mind please.

 

Measure: Responses =counta([EMPLID])

Measure: RankExclTEST:

 

 

=IF(AND(IF ( HASONEVALUE ( LAPIS_EXIT_LOAD[ITEM_DESCR] ), VALUES (LAPIS_EXIT_LOAD[ITEM_DESCR]) ) <>"Other",
IF ( HASONEVALUE ( LAPIS_EXIT_LOAD[ITEM_DESCR] ), VALUES (LAPIS_EXIT_LOAD[ITEM_DESCR]) ) <>"Retirement"
)
,RANKX(ALL(LAPIS_EXIT_LOAD[ITEM_DESCR]),CALCULATE([Responses]),,0),blank())

 

 

Pivot table screenshot. Note I am using all sections: Filters, Columns, Rows, Values.

 

rankxEx ver2.png

 

Data Model

ExitSurvey_PQ4_PowerPivot_DataModel.png

 

0 Replies