Forum Discussion

mufy_cw's avatar
mufy_cw
Regular Visitor
7 months ago
Solved

Diffucty in getting rank function when bridge table is used

As suggested by most of them I created a bridge table. I first unpivoted my sales table in dataflows which expanded to 35 crore lines. There is no other way to control it as because it just have pfc,...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    6 months ago

    Hello mufy_cw 

     

    Even though REGIONDATA is directly connected to BASE MEASURE, the ranking issue occurs because measures like RANKX evaluate over a virtual table.

    If you build a virtual table using only MODIFIED BRANDS and COMPANY BY SBU, any slicers or filters on other BASE MEASURE columns won’t be included automatically. This means the rank calculation doesn’t fully honor the filter context, even with the relationship in place.

    By using SUMMARIZE to create the virtual table, you ensure all relevant filters from BASE MEASURE are applied, preserve the connection with REGIONDATA, and prevent unnecessary data model expansion. That’s why the revised measure works as expected.

    Regards,

    Microsoft Fabric Community Support Team.
     



muffycw's avatar
muffycw
Frequent Visitor
4 months ago

Hi v-karpurapud 
Thank you so much for getting back and providing solutions but by the time I saw your reply I already found the correct rank dax.

I explained everything from scratch to microsoft co-pilot I explained the data, the model, the table, mat 5 dax and then after couple of tries co-pilot gave me the working solution. This is the dax provided, it is so simple and we were just trying complex formulas.

MAT 5 Rank =
IF (
    ISINSCOPE ( 'BASE MEASURES'[MODIFIED BRANDS] )
        && ISINSCOPE ( 'BASE MEASURES'[COMPANY BY SBU] ),
    RANKX (
        ALLSELECTED (
            'BASE MEASURES'[MODIFIED BRANDS],
            'BASE MEASURES'[COMPANY BY SBU]
        ),
        [MAT 5],
        ,
        DESC,
        DENSE
    )
)

I tried it on my current filtered data so it seems to be working fine, havent tested it thoroughly with multiple filters and data selections, however for now we can keep it as the solution. 

If I encounter any issue with this dax later, I will post here again.

Thanks everyone who tried to help me.
Cheers!!!



v-karpurapud's avatar
v-karpurapud
Icon for Community Support rankCommunity Support
4 months ago

Hi muffycw 

Glad to hear the issue has been resolved😊. If you have any other questions, feel free to contact us. We're here to help.

 

Regards,

Microsoft Fabric Community Support Team.