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.

Zanqueta's avatar
Zanqueta
Icon for Super User rankSuper User
10 months ago

Hi Amudha_Kumaran, 
This is a common challenge in Live Connection models where calculated columns are not allowed. The solution involves creating a DAX Measure that acts as a row-level flag, determining if the current row holds the maximum Days value for its respective Activity_ID. Crucially, you must then apply this measure as a Visual Filter on your table (set to where the value is 1) to display only the latest records.

Try this:

Is Latest Flag =
VAR CurrentActivityID = MAX('YourTable'[Activity_ID])
VAR CurrentDays = MAX('YourTable'[Days])
VAR MaxDaysForActivity =
    CALCULATE(
        MAX('YourTable'[Days]),
        ALL('YourTable'[Days], 'YourTable'[Counterparty]), -- Remove row-level context filters
        'YourTable'[Activity_ID] = CurrentActivityID       -- Keep filter for the current Activity_ID
    )
RETURN
    IF(
        CurrentDays = MaxDaysForActivity,
        1, -- Latest record
        0  -- Not the latest record
    )


If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your KUDO if you find it useful.

  • Amudha_Kumaran's avatar
    Amudha_Kumaran
    Regular Visitor
    10 months ago

    Hi Zanqueta , 

     

    Thanks for the quick response. I still don't see the current flags. All are showing 1

    • Zanqueta's avatar
      Zanqueta
      Icon for Super User rankSuper User
      10 months ago

      Hi Amudha_Kumaran ,

      Thanks for the feedback. If the measure is consistently returning 1 for every row, it usually means that the Row Context is not being properly filtered or the MaxDaysForActivity calculation is incorrectly seeing the same Days value as the current row.

      The most robust way to ensure the calculation ignores the row-level filters (like the specific values of Days and Counterparty for that row) is to use ALL('YourTable') inside the CALCULATE to clear all internal table filters, and then explicitly re-apply the filter for the current Activity_ID.



      maybe you can try:

      Is Latest Flag =
VAR CurrentActivityID = MAX('YourTable'[Activity_ID])
VAR CurrentDays = MAX('YourTable'[Days])
VAR MaxDaysForActivity =
    CALCULATE(
        MAX('YourTable'[Days]),
        ALL('YourTable'),                                  -- Clear all filters from the table
        KEEPFILTERS('YourTable'[Activity_ID] = CurrentActivityID) -- Only keep the filter for the current Activity_ID
    )
RETURN
    IF(
        CurrentDays = MaxDaysForActivity,
        1, -- Latest record
        0  -- Not the latest record
    )