Forum Discussion

Ciria's avatar
Ciria
Advocate III
8 years ago

Multiple Levels/Drills Rank Measure (Problem)

Dears: :smileysad:   I'm experiencing  some problems with a Matrix created with several levels/drills in rows and a measure to Rank data in every single level.   For the moment I'm just defining ...
Need Help
Tips and Tricks
Ciria's avatar
Ciria
Advocate III
8 years ago

Hi v-yulgu-msft

 

First of all thank you for the answer, it is really helpful. It has solved 50% of my problem.

 

Now, I can drill down in multiples levels getting proper ranking results. The good thing is this method  makes drill down possible even in multiple levels, not only two, opening a wide range of opportunities by using IF and NOT functions.

 

But still the second problem I mentioned is present.

  1. When I click over a visual, narrowing down the list on table, all families/countries are shown, even when they don't have any result to show up.
  2. Same thing when I drill down in one family, Table is not showing me only those results with values, but all countries, regardless they have sales or not.
  3. I've attached two pictures for further explanation.

 

How can amend the formula to only show values with figures?

 

Using VALUES instead of ALL in RANKX function doesn't work.... Perhaps a combination of FILTER+VALUES like EnterpriseDNA (Sam Mckay did here) but my DAX skills are still limited :-(

https://www.youtube.com/watch?v=IyrNH7aD_qk&t=408s

 

Awaiting for your feedback :-)

    • Ciria's avatar
      Ciria
      Advocate III
      8 years ago

      Dears:

       

      Any idea about how remove those countries without sales on the list?

       

      You need ALL function there to remove the filter context you have with "Country_Name" on Rows, otherwise all rows will be ranked as "1".

       

      I have temporaly managed the situation, using "Visual Filters", selecting "Total Sales Greater than 0", but I would like to know if there is a way to do it with DAX.

       

      Thanks in advance for the support :-)

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hello 

         

        Is there a solution for this yet? tried the examples in the above PBIX and Youtube video but no luck. 

         

        I've got the "Sub category" Ranking to work funnily enough but not the main category.

         

        My DAX is: 

         

        Index Rank =
        VAR IsCountryFiltred =
            ISFILTERED ( Country[Country] )
        VAR IsTitleSeasonFiltered =
            ISFILTERED ( Programme[Title Season] )
        VAR IsTitleSeason =
            HASONEVALUE ( Programme[Title Season] )
        RETURN
            IF (
                ISBLANK ( [RTG Index] ),
                BLANK (),
                IF (
                    IsCountryFiltred && IsTitleSeasonFiltered,
                    RANKX ( ALL ( Country[Country] ), [RTG Index],, DESC, SKIP ),
                    IF (
                        IsTitleSeasonFiltered && NOT IsCountryFiltred,
                        RANKX ( ALL ( Programme[Title Season] ), CALCULATE ( [RTG Index] ) ),
                        BLANK ()
                    )
                )
            )

         Any help would be grand. 

         

        Thanks

         

         

        Sub Category Rank working with DaxMain category only works with I remove the Sub catogory from the Matrix