topic Re: RankX function across multiple Dimension Tables in DAX Commands and Tips

RankX function across multiple Dimension Tables

abhiram342 — Wed, 19 Aug 2020 03:41:36 GMT

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

Re: RankX function across multiple Dimension Tables

amitchandak — Wed, 19 Aug 2020 03:51:49 GMT

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

Re: RankX function across multiple Dimension Tables

Greg_Deckler — Wed, 19 Aug 2020 04:37:13 GMT

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.

Re: RankX function across multiple Dimension Tables

Anonymous — Wed, 19 Aug 2020 05:32:03 GMT

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)

Re: RankX function across multiple Dimension Tables

abhiram342 — Wed, 19 Aug 2020 05:48:48 GMT

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

Re: RankX function across multiple Dimension Tables

abhiram342 — Wed, 19 Aug 2020 06:11:28 GMT

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

Re: RankX function across multiple Dimension Tables

Anonymous — Wed, 19 Aug 2020 14:02:24 GMT

// 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

Re: RankX function across multiple Dimension Tables

abhiram342 — Wed, 19 Aug 2020 17:02:32 GMT

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 __top3Customers

Thanks,

Abhiram