Forum Discussion
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, zone, region, metro and as I said 180 columns of sales value. So there is nothing i could aggregate or partition. The table name is regiondata. Then the other table called "base measure" (it is like a dimension table) which has other information of the pfc codes which is around 20-30 columns or other details about the pfc. This table has unique pfc. As discussed earlier another table "ABC Flag" which has pfc, division and market where i said pfcs are repeating as same pfc could be sold in multiple markets. Since pfcs are repeating i cannot connected abc flag table to region table directly. So connected region data to my "base measure" table many to one and then i connect "base measure" to "ABC flag" one to many. So now base measure is my bridge table. This is the formula for calculating my MAT 5 where I am taking market from abc flag which is not directly connected to regiondata.
MAT 5 = VAR EndDate = MAX ( 'CALENDAR'[Date] ) RETURN CALCULATE ( SUM ( REGIONDATA[Value] ), REGIONDATA[Type] = "VALUE", DATESINPERIOD ( 'CALENDAR'[Date], EndDate, -12, MONTH ), TREATAS ( VALUES ( 'ABC FLAG'[PFC] ), REGIONDATA[PACK CODE] ) )
This works perfectly when I add modified brands and compny by sbu from base measure table in the matrix table and division column from abc flag table as a slicer.
The real problem is I am unble to get the rank properly it messes up. This is the rank formula I am using which requires to rank based on combination of modified brands and company by sbu.
Mat 5 rank = VAR CombinedTable = ALLSELECTED( 'BASE MEASURES'[MODIFIED BRANDS], 'BASE MEASURES'[COMPANY BY SBU] ) RETURN IF( ISINSCOPE('BASE MEASURES'[MODIFIED BRANDS]) && ISINSCOPE('BASE MEASURES'[COMPANY BY SBU])&& [MAT 5] > 0, RANKX( CombinedTable, [MAT 5],,DESC,Dense ) )
If I bring all the columns I am using in the matrix table like join modified brands and company by sbu together in a single column then a simple rank dax works perfectly.
But i would be needing most of the columns from base measures to use as filter so I will have to bring all 20-30 columns to regiondata file which will again end up in increasing the refresh time and I dont think that is practical as it is connected to region data directly so why should i bring it over.
Can anyone please help because I am going crazy to get the rank properly.
I can make my life easy if I bring all the tables like base measure columns and abc flag columns to region data by doing left join but it then explodes to 48-50 crore lines (487 million ) which takes approx 3-4 hours to refresh in dataflows and when importing it in power bi it takes another 2-3 hours
Any help or guidance is much appreciated!!
Thanks!
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.
13 Replies
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!!!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.
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Hi mufy_cw
Thank you for reaching out to the Microsoft Fabric community forum.
The issue isn’t due to missing columns or relationships. The ranking problem occurs because your RANKX measure evaluates over a table that doesn’t fully capture the filter context from BASE MEASURE.
Since REGIONDATA is connected to BASE MEASURE (bridge) and BASE MEASURE to ABC FLAG, RANKX only considers the columns explicitly referenced. Other slicers or filters on BASE MEASURE are ignored, causing incorrect ranks.
To resolve this, create a virtual table using SUMMARIZE including MODIFIED BRANDS and COMPANY BY SBU, then rank over it:
MAT 5 Rank = VAR RankTable = SUMMARIZE( 'BASE MEASURES', 'BASE MEASURES'[MODIFIED BRANDS], 'BASE MEASURES'[COMPANY BY SBU], "MAT5Value", [MAT 5] ) RETURN IF( ISINSCOPE('BASE MEASURES'[MODIFIED BRANDS]) && ISINSCOPE('BASE MEASURES'[COMPANY BY SBU]) && [MAT 5] > 0, RANKX(RankTable, [MAT5Value], , DESC, Dense) )
This approach preserves the full filter context, avoids inflating REGIONDATA, and ensures the ranking works correctly in your matrix visuals.
If you have any more questions, please let us know and we’ll be happy to help.
Regards,
Microsoft Fabric Community Support Team.
Hi v-karpurapud
Thank you so much for the reply. I will try this and post a message here whether it worked or not. I just wanted to understand the regiondata is directly connected to base measure why it is having difficulty in filtering and calculating?
Thanks!
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.
Hi mufy_cw
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.Your solution didnt work mate. I am really sorry for the late reply I was super busy.
When I am copying this formula
Mat 5 rank = VAR RankTable =SUMMARIZE('BASE MEASURES','BASE MEASURES'[MODIFIED BRANDS],'BASE MEASURES'[COMPANY BY SBU],"MAT5Value", [MAT 5])RETURNIF(ISINSCOPE('BASE MEASURES'[MODIFIED BRANDS]) &&ISINSCOPE('BASE MEASURES'[COMPANY BY SBU]) &&[MAT 5] > 0,RANKX(RankTable, [MAT5Value], , DESC, Dense))I am getting error MAT5VALUE cannot be determined. Either the column doesnt exist or there are no current row for this columnHi muffycw
Sorry for the delay response. The error is happening because "MAT5Value" is not a measure t’s a column created inside a virtual table. In DAX, columns created inside SUMMARIZE cannot be referenced like measures inside RANKX, which is why it says it “cannot be determined.”
To fix this, you need to either evaluate the measure inside RANKX using CALCULATE, or properly materialize the column using ADDCOLUMNS.
Try this version:
MAT 5 Rank = VAR RankTable = SUMMARIZE( ALLSELECTED( 'BASE MEASURES'[MODIFIED BRANDS], 'BASE MEASURES'[COMPANY BY SBU] ), 'BASE MEASURES'[MODIFIED BRANDS], 'BASE MEASURES'[COMPANY BY SBU] ) RETURN IF( ISINSCOPE('BASE MEASURES'[MODIFIED BRANDS]) && ISINSCOPE('BASE MEASURES'[COMPANY BY SBU]) && [MAT 5] > 0, RANKX( RankTable, CALCULATE([MAT 5]), , DESC, DENSE ) )The key change is CALCULATE([MAT 5]) inside RANKX, which forces the correct filter context for each row being ranked.
I hope this clarifies things. Let me know what you find after trying these steps; I'm happy to assist further if needed.
Regards,Microsoft Fabric Community Support Team.
this is the setup for reference
below is mat 5 dax
MAT 5 =VAR EndDate =MAX ( 'CALENDAR'[Date] )RETURNCALCULATE (SUM ( REGIONDATA[Value] ),REGIONDATA[Type] = "VALUE",DATESINPERIOD ('CALENDAR'[Date],EndDate,-12,MONTH),TREATAS (VALUES ( 'ABC FLAG'[PFC] ),REGIONDATA[PACK CODE]))
this is the output table currently with wrong rankingHi muffycw
Thank you for providing the complete setup, that really helps clarify things.
The issue isn't from TREATAS or your model, but rather from how RANKX evaluates the measure during the ranking process.Previously, the ranking was assessed in a context that did not recalculate the measure for each row accurately. To address this, it is necessary to enforce the correct filter context within RANKX.
There is no need to create a separate summarized table. Instead, allow RANKX to iterate over the relevant columns and evaluate the measure for each row.
Please use the following version:MAT 5 Rank = IF( ISINSCOPE('BASE MEASURES'[MODIFIED BRANDS]) && ISINSCOPE('BASE MEASURES'[COMPANY BY SBU]) && [MAT 5] > 0, RANKX( ALLSELECTED( 'BASE MEASURES'[MODIFIED BRANDS], 'BASE MEASURES'[COMPANY BY SBU] ), CALCULATE([MAT 5]), , DESC, DENSE ) )
The critical element is the use of CALCULATE([MAT 5]) within RANKX, as this ensures the measure is recalculated for each brand and company combination during the ranking process.If you have any more questions, please don't hesitate to contact us and we will assist you further.
Regards,
Microsoft Fabric Community Support Team
Awaiting reply on the above issue
