DAX Ranking Issue

[Taha_M]

I have to create a measure to rank each product within it's category based on total sales. I have used RANK function for this. The first measure (pic 1) correctly achives that. However, the second measure (pic 2) gives rank 1 to all proucts incorrectly. I have researched and tried to debug and I have come across some suggestions that use of ADDCOLUMNS in SourceTable is the issue b/c the [Total Sales] measure gives the same subtotal of sales to all products hence the rank 1 to all of them. However, I have checked through DAX query view, and the Source Table of both measures are identical, which means the issue is not here.

The issue seems to as per my understanding lie in breakage of data lineage when ADDCOLUMNS is used i.e. the Source Table is not able to tie back to the base Product_Data table in the data model. Can someone help debug why is that (if that is the underlying isse) and how to resolve it?

     

[theov]

The ranking example in this video is a good example: https://youtu.be/D_6kPwnByWQ?si=8SPeJu_2EGKtfSnT

[KNP]

Hi @Taha_M,

I think I understand the issue.

(For future reference, it's much easier to provide answers if you also paste code and data, rather than just screenshots).

Try this and see if you get what you're looking for.

Product Rank = 
    VAR SourceTable = 
        ADDCOLUMNS(
            ALLSELECTED(
                Product_Data[CategoryID]
                , Product_Data[ProductID]
                , Product_Data[ProductName]
            ), 
            "@TotalSales", [Total Sales]
        )
    
    RETURN
        RANK(
            SKIP
            , SourceTable
            , ORDERBY([@TotalSales], DESC)
            ,, PARTITIONBY(Product_Data[CategoryID])
        )

[Taha_M]

Hi @KNP. Firstly, thankyou for replying. This was my first post so sure your advice taken for the future!

I have tried your code and yes it gives correct ranking.
Similarly, if I remove the ProductID column from the matrix (since this column isn't passed to ALLSELECTED in my [Product Rank wrong] measure), I see that my measure gives correct ranking.

From this, I have come to the conclusion that if I include ProductID column in the matrix, during the evaulation of the measure for each row of the matrix it is filtering the Source Table to only 1 product (in the data model each product name has a unique product id and category id, I have attached the picture of data in the model beneath the text - it is a small practise dataset). Hence, since the ranking is performed for only a single product visible in the Source Table, it gets ranked 1. 

This however contradicts what the maestros at SQLBI has to say about the  behavior of ALLSELECTED(<columnName>) when used as  table argument: The reference article is: link 
"ALLSELECTED returns the values of a column as filtered by the last shadow filter context. The reader might have noticed that there is no active iteration when we call ALLSELECTED. Thus, there are no shadow filter contexts to activate. As a result, all the product names are returned because neither Color nor Brand filter the Product[Product] column – although it is cross-filtered............ALLSELECTED works on a column and checks whether that column is filtered by a shadow filter context, ignoring any cross-filter."

They say that the column passed to ALLSELECTED should not be cross-filtered by other columns of that same table since ALLSELECTED does not take into account the filter context. What seems to happen in our case violates this i.e. ProductID column is indeed cross-filtering CategoryID and ProductName columns and hence incorrectly narrowing down Source Table to single product  each time the measure is evaluated within the matrix. 

I would like to hear your thoughts if this is what you were  speculating the issue to be aswell, and what do you think of my take at this SQLBI article.

[v-aatheeque · Accepted solution]

Hi @Taha_M 

To better reproduce your issue, could you share a small sample of your data with the expected rank result?(not as a screenshot).Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[v-aatheeque]

Hi @Taha_M 

To better reproduce and troubleshoot your scenario, we kindly request a small sample dataset/file. Please share it at your convenience, as this will help us provide a more accurate and tailored solution.