Forum Discussion
How can you keep rank on a visualization using a slicer?
Hi,
I have successfully been able to write a query which keeps rank of depots in a visualization with only one selected; so when a new depot is chose the line graph still keeps the rank versus the other depot, my problem is that my values need to be from a slicer and not a single measure.
At the moment, I don't undertsand what the visualization is doing, I believe it has created a aggregate of all the ranks for each depot but I am not sure. Any help on this would be much appreciated, currently column query below:-
Column =
RANKX(ALL('Depot KPI PBI Data 1'),
CALCULATE(([Select measure 1]),
ALLEXCEPT('Depot KPI PBI Data 1', 'Depot KPI PBI Data 1'[Depot],'Depot KPI PBI Data 1'[WEEK_OF_YEAR]))
,,DESC,Dense)
Below is a screenshot of the dashboard with the slicer on the left and table which I expect to be reflected in the middle and what is currently returned from the query on the right. Depot selected is Avonmouth.
Any help would be greatly appreciated!!
Thanks
Hi Anonymous ,
According to your description, you except a rank of each depot when the depot is filtered. You can rank according to each depot, modify your formula like:
Column =
RANKX (
FILTER (
'Depot KPI PBI Data 1',
'Depot KPI PBI Data 1'[Depot] = EARLIER ( 'Depot KPI PBI Data 1'[Depot] )
),
CALCULATE (
( [Select measure 1] ),
ALLEXCEPT (
'Depot KPI PBI Data 1',
'Depot KPI PBI Data 1'[Depot],
'Depot KPI PBI Data 1'[WEEK_OF_YEAR]
)
),
,
DESC,
DENSE
)
If you prefer a measure, it would be like this:
Measure =
RANKX (
FILTER (
ALL('Depot KPI PBI Data 1'),
'Depot KPI PBI Data 1'[Depot] = MAX ( 'Depot KPI PBI Data 1'[Depot] )
),
CALCULATE (
( SUM([Select measure 1]) ),
ALLEXCEPT (
'Depot KPI PBI Data 1',
'Depot KPI PBI Data 1'[Depot],
'Depot KPI PBI Data 1'[WEEK_OF_YEAR]
)
),
,
DESC,
DENSE
)
It works fine with the formula, bellow is my sample, it maybe can help you to understand.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , What you giving should be measure rank, column rank can not be dynamic (it can not take slicer filter
Try measure like
RANKX(ALLSELECTED('Depot KPI PBI Data 1'),
CALCULATE(([Select measure 1]),
filter(allselected('Depot KPI PBI Data 1'), 'Depot KPI PBI Data 1'[Depot] = max('Depot KPI PBI Data 1'[Depot])
&& 'Depot KPI PBI Data 1'[WEEK_OF_YEAR] = max('Depot KPI PBI Data 1'[WEEK_OF_YEAR])) )
,,DESC,Dense)For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns 
v-yanjiang-msftCommunity Support
Hi Anonymous ,
According to your description, you except a rank of each depot when the depot is filtered. You can rank according to each depot, modify your formula like:
Column =
RANKX (
FILTER (
'Depot KPI PBI Data 1',
'Depot KPI PBI Data 1'[Depot] = EARLIER ( 'Depot KPI PBI Data 1'[Depot] )
),
CALCULATE (
( [Select measure 1] ),
ALLEXCEPT (
'Depot KPI PBI Data 1',
'Depot KPI PBI Data 1'[Depot],
'Depot KPI PBI Data 1'[WEEK_OF_YEAR]
)
),
,
DESC,
DENSE
)
If you prefer a measure, it would be like this:
Measure =
RANKX (
FILTER (
ALL('Depot KPI PBI Data 1'),
'Depot KPI PBI Data 1'[Depot] = MAX ( 'Depot KPI PBI Data 1'[Depot] )
),
CALCULATE (
( SUM([Select measure 1]) ),
ALLEXCEPT (
'Depot KPI PBI Data 1',
'Depot KPI PBI Data 1'[Depot],
'Depot KPI PBI Data 1'[WEEK_OF_YEAR]
)
),
,
DESC,
DENSE
)
It works fine with the formula, bellow is my sample, it maybe can help you to understand.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
