RankX function across multiple Dimension Tables

[abhiram342]

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

[Anonymous]

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

[abhiram342]

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 

[harshnathani]

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)

[abhiram342]

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

[Greg_Deckler]

@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.

[amitchandak]

@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/3...

[abhiram342]

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