Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.


Create a drilldown on a chart that uses a rank filter



I have created a bar chart that shows top 5 and bottom 5 of this month vs. last month value.  This shows the greatest drivers of change (either positive or negatve) vs. last month.




The measures used are:


Values: MoM Market = [UoM TM Market] - [UoM LM Market]  // (this month - last month)
Axis: PH3 Description // a category field for product descriptions
Filter 1: MoM Market is not 0
Filter 2:  RankL3MoMMarket is not 0
The ranking filter measure is as follows:
RankL3MoMMarket = IF(OR([Rank MoM Market L3 Bottom]<6,[Rank MoM Market L3 Top]<6), [MoM Market],0) 
Which is made up from these two measures:
Rank MoM Market L3 Top = rankx(ALLSELECTED(SKUData[PH3 Description]),[MoM Market],,Desc, Dense)
Rank MoM Market L3 Bottom = rankx(ALLSELECTED(SKUData[PH3 Description]),[MoM Market],,Asc, Dense)

So I therefore get the top 5 and bottom 5 of change vs. last month for category L3 products


Okay, so here is the question.


I now want to put in a drilldown into the same chart.   The chart has told me what the greatest driver of change is at Level 3, so I want to drill further into that area, from Level 3 categorisation into, e.g., a Level 5 categorisation.  (example, level 3 might be "clothes" and level 5 might be "socks", "shirts" & "hats")


But every time I try to create this I end up with the chart expanding to show all the categories, not just the top and bottom 5.  example:




I've tried creating the same filter for L5, and applying the same "is not 0", but it doesn't behave like I expected  


What is the behaviour of the chart and how can I fix it?



Community Support
Community Support

Hi @dapperscavenger ,


You may change measure [RankL3MoMMarket] like DAX below.



RankL3MoMMarket =
IF (
    HASONEVALUE ( [Level 5 categorisation] ),
    [MoM Market],
    IF (
        OR ( [Rank MoM Market L3 Bottom] < 6, [Rank MoM Market L3 Top] < 6 ),
        [MoM Market],



Best Regards,



Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ah, that did not work.  I've been googling around and I think I have asked for the impossible.  PowerBI does not seem to have this functionality.


So I will find another way to try to represent the data


Thank you!

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric certifications survey

Certification feedback opportunity for the community.