Forum Discussion

2366's avatar
2366
Icon for Helper I rankHelper I
4 years ago
Solved

RANKX across multiple tables

Hi,   I have the below tables in my data model - With the current RANKX expression I am able to Rank product sales across all cities but my desired output is to calculate sales of my products acros...
DAX
RANKX
  • amitchandak's avatar
    amitchandak
    4 years ago

    2366 , Try Rank like

     

    RANKX(Summarize(All('Fact Sales'),'Fact Sales'[ProductID],'Dim Country'[Country] ), SUM(Sales),,DESC,)

     

    Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

Jos_Woolley's avatar
Jos_Woolley
Icon for Solution Sage rankSolution Sage
4 years ago

Hi,

In this case, auto-exist will not apply and so you need to be careful to avoid ending up with a visual which contains additional entries due to the cross-join between the two tables. So I would try something like:

 

Measure =
VAR T1 =
    ADDCOLUMNS(
        ADDCOLUMNS(
            'Fact Sales',
            "Country", RELATED( 'Dim Country'[Country] )
        ),
        "MyRank",
            RANKX(
                ALL( 'Fact Sales' ),
                'Fact Sales'[Sales],
                ,
                ,
                DENSE
            )
    )
RETURN
    MINX(
        T1,
        [MyRank]
    )

 

Regards