Complex Ranking in a Star Schema

[mark_endicott]

Hello, 

We have been struggling with getting Rankx to work, when theoretically a user could want to include a column from any of the tables in our star schema model in a table, and it not affect the ranking of Quantity against the Identifier. Due to the size of our model, it will not be possible to use CROSSJOIN and ALLSELECTED to join the tables together.

The DAX we are currently working with is split out into two measures, the first to aggregate the base measure so that it respects any filters applied outisde the visual, but ignoring those from within the visual (so long as they are not supplied by the rank):

Q_by_IDENTIFIER = 
VAR CurrentIDENTIFIER =
    SELECTEDVALUE ( 'PRODUCT_ATTRIBUTES'[IDENTIFIER] )
RETURN
    CALCULATE (
        [Total qty],
        ALLSELECTED ( 'TABLE_1' ),
        ALLSELECTED ( 'TABLE_2' ),
        ALLSELECTED ( 'TABLE_3' ),
        ALLSELECTED ( 'TABLE_4' ),
        ALLSELECTED ( 'TABLE_5' ),
        'PRODUCT_ATTRIBUTES'[IDENTIFIER] = CurrentIDENTIFIER
    )

The second measure supplies the Rank, and the filter we want it to apply, e.g. the identifier:

RANKX ( ALLSELECTED ('PRODUCT_ATTRIBUTES' ), [Q_by_IDENTIFIER], ,DESC, DENSE )

However this only produces the correct rank against columns from 'PRODUCT_ATTRIBUTES', as soon as you introduce a column from another table, each item is ranked 1. So to me it is clear the first measure is not working as expected. 

Any help would be greatly appreciated.

[littlemojopuppy]

Hi @mark_endicott can you share any data?

[mark_endicott]

@littlemojopuppy - thanks for the offer of help, I can send over a sample dataset, with a few different ways we've tried in folders. Can you suggest a way to share this with you?

A breakdown of what I can send is below: 

SimpleRank - is a simplistic view that only ranks on Total qty
RankStg2 - is a 2 stage calculation, that factors in Total value to remove ties. 

RankStg3 - is the same calc but split out to 3 measures.


Final Rank Solution is the end game by rolling all calcs into one measure, when testing this has computed much quicker than splitting out. 


As you'll see from the table in the report, each rank only works when using the PRODUCT_ATTRIBUTES table. And as mentioned in my last message, we cannot use CROSSJOIN and/ALLEXCEPT due to the size of the data model (currently 13 GB).

[littlemojopuppy]

Hi @mark_endicott sorry for the delay in replying.  Long day yesterday.

Best way to share would be to drop your pbix into OneDrive, Google Drive, etc. and share a link so I can download.

[mark_endicott · Accepted solution]

Thanks for the offer of help, but we got this one sorted, the DAX we were using was pretty much correct, just swapped out the dims for the fact inside the ALLSELECT (that was more for future proofing) and took a filter off the table, which was masking that the DAX was actually working.