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, ThxAlot  & Zanqueta ,

 

Thanks for your responses. I have somehow created a measure to find the latest record with max days. But, there are duplicates with same activity id, max days & counterparty. There is no other unique field in the table to use for tie-breaking. This is where I'm stuck. Trying to handle ties in the latest record

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

Hi Amudha_Kumaran 

Now, it worked:

Is Latest Flag = 
VAR CurrentActivityID = MAX('tbl'[Activity_ID])
VAR CurrentDays = MAX('tbl'[Days])
VAR CurrentCounterparty = MAX('tbl'[Counterparty])

VAR MaxDaysForActivity =
    CALCULATE(
        MAX(tbl[Days]),
        ALL(tbl),
        KEEPFILTERS(tbl[Activity_ID] = CurrentActivityID)
    )

VAR MaxCounterpartyForMaxDays =
    CALCULATE(
        MAXX(
            FILTER(
                ALL(tbl),
                tbl[Activity_ID] = CurrentActivityID
                && tbl[Days] = MaxDaysForActivity  -- Filtra apenas linhas com o Max Days
            ),
            tbl[Counterparty]
        )
    )

RETURN
    IF(
        CurrentDays = MaxDaysForActivity && CurrentCounterparty = MaxCounterpartyForMaxDays,
        1, -- Latest record (Max Days AND Max Counterparty)
        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.