Jun 23 2021 06:31 PM - edited Jun 29 2021 11:17 AM
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.
Data Model