Rankx on a star schema (Rankx from multiple tables / dimensions)

[TopHat]

I have two dimensions and a fact as below for example:

Dimensions with columns in brackets

================================

D1 (C1)

D2(C2)

Fact

============

F(FC1)

Relationship

==========

Both Dimensions connect to the Fact on a one-to-many lets say the foreign keys in the Fact are D1F and D2F.

My measure 

R =

RANKX(

ALLSELECTED(D1(C1)),

CALCULATE(COUNT(F(FC1)))

,,DESC,Dense

)

I have a table with R,D1, count(F1)

That comes up right. Ans since I wanted top 10 I used the Filter pane and TopN to return the number of rows.

What I now need is on the same table to show  D2 along. 

Now no matter what I have tired I could not posible add D2 in the measure R.

I either ended up with wrong Rank or all the rows from D2 would appear.

Please would someone throw some light on how to use Rankx usign multiple dimensions connecting to a fact.

Thanks

[daxer-almighty]

Here's the partial solution to the problem of drillthrough and relative ranking as promised. Please, study the solution carefully and in depth. You'll have to understand it to adjust it to your model. You'll need to replicate some of the Garment table to achieve what you want. Take a good look at which columns from which tables have been put in which visuals. This is of utmost importance. And remember that table that should not be visible in the UI should always be hidden not to confuse users.

https://1drv.ms/u/s!ApyQEauTSLtOgZlS0EJO8F29gGxgPQ?e=MG9c9n

[Anonymous]

Hi there, @TopHat.

As mentioned, I've found a fully dynamic and general solution but it requires some DAX wizardry and for the time being I only have a sketch, not yet fully functional. I'll work on this and once a full general solution is assembled, I'll place a link to the file in here. Please bear with me. Thanks.

[Anonymous · Accepted solution]

OK, this measure does it for any combination of colour and garment:

Rank = 
var CurrentPrice = [Total Price]
var CurrentPriceIsNotBlank = NOT( ISBLANK( CurrentPrice ) )
return
if( CurrentPriceIsNotBlank,
    var ColorInScope = ISINSCOPE( Colour[Colour] )
    var GarmentInScope = ISINSCOPE( Garment[Garment] )
    return
    SWITCH( TRUE(),
        ColorInScope && GarmentInScope,
            var TableToRankOverWhenBothInScope =
                CALCULATETABLE(
                    ADDCOLUMNS(
                        SUMMARIZE(
                            F,
                            Garment[Garment],
                            Colour[Colour]
                        ),
                        "@TotalPrice", [Total Price]
                    ),
                    ALLSELECTED( F )
                )
            var Result =
                RANKX(
                    TableToRankOverWhenBothInScope,
                    [@TotalPrice],
                    CurrentPrice,
                    DESC, Dense
                )
            return
                Result,
        ColorInScope,
            RANKX(
                ALLSELECTED( Colour[Colour] ),
                [Total Price],
                CurrentPrice,
                DESC, Dense
            ),
        GarmentInScope,
            RANKX(
                ALLSELECTED( Garment[Garment] ),
                [Total Price],
                CurrentPrice,
                DESC, Dense
            )
    )
)

So, you can rank using this measure either the colours, or the garments or both at the same time. Here's the model I created:

When creating models make sure that they do not have referential integrity issues. Otherwise you'll be hunting subtle bugs. [Total Price] is just the old plain sum over F[Price].

[TopHat]

Thanks for that, it's perfect.

What would you have to do if you wanted to find what the rank of a certain Garment & Colour is and show it in a card please?

I am looking to use Drillthrough to show the Rank in a Card of the selected Garment & Colour.

[amitchandak]

@TopHat ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[TopHat]

I have mocked this up:

When I add Colour from table D2 I get the below:

What I am after:

I am not being able to use D2 in the RANKX to take that into account.

Hope that is a bit clearer.

Thanks

[amitchandak]

@TopHat , refer these 2

https://community.powerbi.com/t5/DAX-Commands-and-Tips/RankX-function-across-multiple-Dimension-Tabl...

https://community.powerbi.com/t5/Desktop/RANKX-over-two-Dimensions/m-p/643749