Forum Discussion
Rank Based on a Switch Sub Category Measure
- 6 years ago
Here is an approach that creates a virtual table and then filters it to employees of the same type (in the selected context), and then the rank is determined using the Value expression in the RANKX formula. Please try this measure expression. I'm not sure why it doesn't match your rank values, but it does calculate rank within each type group. You can adapt it to get your desired result. Did you rank based on Employee ID?
Rank by Type = VAR currenttype = [Sales Employee Type] VAR overallrank = [Overall Rank by Employee ID] VAR summary = ADDCOLUMNS ( ALLSELECTED ( Employees[Employee ID] ), "@type", [Sales Employee Type], "@rank", [Overall Rank by Employee ID] ) RETURN RANKX ( FILTER ( summary, [@type] = currenttype ), [@rank], overallrank, ASC )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Anonymous , In case you need to create Slicer on Measure to filter, you have to follow a segmentation approach.
If you want to filter in filter clause, then you have to force values to row context
Rank column
City Rank = RANKX(all(Geography[City]),[Sales])
filter
Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )
For filter approach
You need have independent table and create new measures with values to make the filter work
refer
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
If you looking for measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://www.youtube.com/watch?v=vlnx7QUVYME