Nested Rank in DAX

Question

I have two tables of data:

1.Fact:

ImaginaryFact = 
DATATABLE (
    "FruitKey", INTEGER,
    "Amount",   INTEGER,
    { 
        { 1, 1 }, 
        { 1, 5 }, 
        { 1, 2 }, 
        { 2, 2 },
        { 2, 3 },
        { 3, 4 },
        { 3, 5 },
        { 4, 2 },
        { 5, 2 },
        { 6, 3 },
        { 7, 8 }
    } )

2.Dimension:

Dimension = 
DATATABLE (
    "FruitKey", INTEGER,
    "Fruit",    STRING,
    "Colour",   STRING,
    { 
        { 1, "Apple",   "Green" }, 
        { 6, "Apple",   "Blue" }, 
        { 7, "Apple",   "Red" },         
        { 2, "Pear",    "Pink" }, 
        { 3, "Orange",  "Green" }, 
        { 4, "Kiwi",    "Green" }, 
        { 5, "Mango",   "Green" }
    } )

With this simple join:

Then I created these two measures:

Amount = SUM( ImaginaryFact[Amount] )

Rank Fruity = 
IF(
    ISFILTERED( 'Dimension'[Colour] ),
    RANKX( ALLSELECTED( 'Dimension'[Colour] ), [Amount] ),
    IF(
       ISFILTERED( 'Dimension'[Fruit] ),
        RANKX( ALLSELECTED( 'Dimension'[Fruit] ), [Amount] )
    )
)

If I create a 'Matrix' then the measure 'Rank Fruity' means I can move up and down between Fruit and Colour and the rank still works AND it blanks out the Total row which I wanted as that row is not filtered:

HERE IS THE PROBLEM When I drill down the hierarchy I want to return blanks for the subtotals same as the Grand Total row is blank ...

You can turn off subtotals, but I'm guessing you want to keep the Amount subtotals, right? — Alexis Olson
Exactly Alexis. I tried 'NOT HASONEVALUE' but it didn't help in the case of say Oranges which DO only have one value! — whytheq
This is an interesting question. At the moment, I'm not sure if it's even possible since the filter context is identical for the fruit subtotal rows whether the matrix is drilled down or not. Unless there is a way to check what level you have expanded in the drilldown with DAX, you may be out of luck. — Alexis Olson
@AlexisOlson I'll add it to the power bi Q&A site to see if any one has a solution or workaround — whytheq

Frostytheswimmer Frostytheswimmer · Accepted Answer · 2018-11-28T21:45:26

Please attempt to use the IF(IsFiltered(),,) Function instead of If(HasOneValue(),,)

https://powerpivotpro.com/2013/03/isfiltered-a-better-way-to-detect-totals/

Nested Rank in DAX

1 Answers