Forum Discussion
Ranking measure with Table variable
Hi sqlguru448 ,
Be aware that making a summarizaition of a full table will bring for sure performance issues, in your case you are using the FACT table that I assume have a huge number of data so be aware of that.
Try the following code:
Measure = var temp_table = FILTER ( SUMMARIZE ( ALLSELECTED ( Customer[Name] ); Customer[Name]; "Total_Sales"; [Sales Amount] ); [Sales Amount] > 0 ) var Ranking = RANKX(temp_table; [Sales Amount]) RETURN RankingThen filter out all rankings above 10.
This has performance issue when other dimesions are added to the visual, that is why I am trying to get top 10 first within variable table and then add other dimensions to it. Thank you.
HI sqlguru448
Try to use this formula to create a measure
Measure 2 =
VAR _RANK= RANKX (
ALLSELECTED ( Customer[Name] ),
CALCULATE ( SUM ( 'Fact'[Sales Amount] ) ))
RETURN
IF ( _RANK <= 10, _RANK)
if not your case, please share some simple sample data and your expected output.
Regards,
Lin
Hi
v-lili6-msft The above DAX gives performance issues as posted in my earlier comments as my real data is in millions, I am adding other dimension columns to the table which is causing the issue. Below is the expected data result for rank, apart from this I have to add other columns from different dimensions.
I am trying to fix it by writing below DAX, this works fine in SSAS dax query but not in Power BI desktop.
The power bi desktop gives rank as all 1's
Measure =var _top10 =TOPN(10, FILTER(SUMMARIZE(all('Fact'), Customer[Name],"Total_Sales", SUM('Fact'[Sales Amount])), [Sales Amount] >0 ), [Sales Amount])RETURNCOUNTROWS(FILTER(_top10, [Total_Sales] >= EARLIER([Total_Sales])))ThanksHi sqlguru448 ,
Be aware that making a summarizaition of a full table will bring for sure performance issues, in your case you are using the FACT table that I assume have a huge number of data so be aware of that.
Try the following code:
Measure = var temp_table = FILTER ( SUMMARIZE ( ALLSELECTED ( Customer[Name] ); Customer[Name]; "Total_Sales"; [Sales Amount] ); [Sales Amount] > 0 ) var Ranking = RANKX(temp_table; [Sales Amount]) RETURN RankingThen filter out all rankings above 10.
Thank you this gave me an idea to modify my dax.
Measure = var _top10 =FILTER(SUMMARIZE(ALLSELECTED('Fact Sales'), Product[Product_Name], Product_Subcategory[Product_subcategory_name, location[state] , "Sales", [Total Sales]), [Total Sales] > 0 )RETURN RANKX(_top10,[Total Sales])Thank you for your help MFelix