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

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