Forum Discussion

Amudha_Kumaran's avatar
Amudha_Kumaran
Regular Visitor
10 months ago
Solved

Power BI desktop - Handling Ties using DAX measure/ RANK

Hi,   I have a table with multiple fields like Activity_Id, Days, Counterparty, and many other fields. My requirement is to display only 1 record per activity _id (fetch only the record with maximu...
Help
  • v-echaithra's avatar
    v-echaithra
    9 months ago

    Hi Amudha_Kumaran ,

    Thanks for the clarification. Based on the behavior you’re seeing, it’s clear that Activity_ID, Days, Counterparty, and all other columns can still repeat, meaning there is no unique row identifier anywhere in the model. When that happens, DAX has no way to distinguish those duplicate rows. A measure can only evaluate values at the column level, it cannot see the underlying physical row or row order inside the engine. Because of this, DAX cannot reliably “pick one random row” when two or more rows are completely identical across every available column.

    Since you are also working with a Live Connection, calculated columns and calculated tables are not an option. That leaves only two feasible and supportable solutions:

    1. Add a surrogate key / row identifier in the source model
    2. Adjust the requirement to a higher grain

    If you can add that key, I can provide a clean, simplified RANK-based measure that will give you exactly one Max-Days row per Activity_ID.

    Hope this helps.
    Thank you.

Amudha_Kumaran's avatar
Amudha_Kumaran
Regular Visitor
10 months ago

Hi Anonymous  & Zanqueta ,

 

Thanks a lot for your responses. However, I found that even counterparty is not a differentiating field in many cases. Duplicates could be due to many other fields. Whatever the case may be, we need only one row for each Activity ID with maximum days. And the issue is, there is no unique row identifier.

v-echaithra's avatar
v-echaithra
Icon for Community Support rankCommunity Support
9 months ago

Hi Amudha_Kumaran ,

Thanks for the clarification. Based on the behavior you’re seeing, it’s clear that Activity_ID, Days, Counterparty, and all other columns can still repeat, meaning there is no unique row identifier anywhere in the model. When that happens, DAX has no way to distinguish those duplicate rows. A measure can only evaluate values at the column level, it cannot see the underlying physical row or row order inside the engine. Because of this, DAX cannot reliably “pick one random row” when two or more rows are completely identical across every available column.

Since you are also working with a Live Connection, calculated columns and calculated tables are not an option. That leaves only two feasible and supportable solutions:

1. Add a surrogate key / row identifier in the source model
2. Adjust the requirement to a higher grain

If you can add that key, I can provide a clean, simplified RANK-based measure that will give you exactly one Max-Days row per Activity_ID.

Hope this helps.
Thank you.