Forum Discussion
RankX function across multiple Dimension Tables
Hi All,
I have two dimension tables and one fact table . I have Product Table (Dimension) , Customer Table ( Dimesnion) and Sales table (Fact). For Each Product, I would like display Top 3 Customer based on Sales.
Output is Table Chart:
Product A Customer1 $100
ProductA Customer2 $200
ProductA Cusotmer3 $300
Can you please help how to acheive using DAX in Power BI
Thanks,
Abhiram
7 Replies
- amitchandakSuper User
abhiram342 , refer this
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415- abhiram342Microsoft Employee
Thanks Amit ! I referred to below blog and using measure simialr to this but da code take long time
>>Top 3 =
SWITCH(
TRUE(),
ISINSCOPE(Customer[Name]), RANKX(ALL('Customer'[Name]),[Total Sales]),
ISINSCOPE('Item'[Brand]), RANKX(ALL('Item'[Brand]), [Total Sales])
)Thanks,
Abhiram
- Greg_DecklerCommunity Champion
abhiram342 - You may find this helpful.
https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452
Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - AnonymousNot applicable
Hi abhiram342 ,
You can use this measure
Top 3 = SWITCH( TRUE(), ISINSCOPE(Customer[Name]), RANKX(ALL('Customer'[Name]),[Total Sales]), ISINSCOPE('Item'[Brand]), RANKX(ALL('Item'[Brand]), [Total Sales]) )Create a Visual Filter where Top3 is less than 3.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)- abhiram342Microsoft Employee
Thanks Harsh ! I have replaced with actual tables and the DAX code is runnig for more than 10 mins . Can you please suggest if we have any alternatives
Thanks,
Abhiram
- AnonymousNot applicable
// Create a measure that for each // customer will tell you if // they are in the top 3 cust for // a product or not. Then use the measure // as a filter for a table visual that will // show you the existing combinations // of Product and Customer and will display // the [Total Sales] measure. Filter the // visual by [Filtering Measure] = 1. [Filtering Measure] = var __oneProductAndCustomerVisible = HASONEVALUE( Customer[CustID] ) && HASONEVALUE( Product[ProdID] ) var __result = if( __oneProductAndCustomerVisible, var __top3Customers = CALCULATE( // Bear in mind that if there // are ties with respect to // [Total Sales], all the qualified // customers will be returned, so // there might be more than 3. topn(3, VALUES( Customer[CustId] ), [Total Sales], DESC ), ALL( Customer ) ) var __currentCustInTop3 = VALUES( Customer[CustID] ) in __top3Customers return 1 * __currentCustInTop3 ) return __result- abhiram342Microsoft Employee
Thanks daxer ! I'm gettng error in below part of code.
Funciton expects table expression for argument but string was used
var __currentCustInTop3 =
VALUES( Customer[CustID] )
in __top3CustomersThanks,
Abhiram