Power BI desktop - Handling Ties using DAX measure/ RANK

[Amudha_Kumaran]

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 maximum values in Days column). However, there could be duplicates even in the maximum row because of other fields. In this case, fetch any random row with maximum days. I have connected to a live model so cannot create calculated columns or tables. Only DAX measure should be used.Need a simplified DAX measure to create a latest record flag as shown in the example

Activity_ID	Days	Counterparty	Is Latest Flag
1	2	A	0
1	3	A	0
1	3	B	1
2	4	A	0
2	6	B	1
3	7	A	1

[v-echaithra]

Hi @Amudha_Kumaran ,

May I ask if you have resolved this issue? Please let us know, if you have any further issues please provide the sample data, we are happy to help.

Thank you.

[Amudha_Kumaran]

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 · Accepted solution]

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.

[v-echaithra]

Hi @Amudha_Kumaran ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance.  Kindly share a sample of the data for further investigation.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Best Regards,
Chaithra E.

Thank you 
Chaithra E.

[ThxAlot]

Easy enough,

If you were able to wrap your head around this measure, DAX is almost no secret for you.

[Amudha_Kumaran]

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

[Anonymous]

Hi @Amudha_Kumaran ,

Please refer below updated Dax.

Is Latest Flag =

VAR _maxDays =

    CALCULATE(

        MAX('Table'[Days]),

        ALLEXCEPT('Table', 'Table'[Activity_ID])

    )

VAR _candidateRows =

    FILTER(

        ALL('Table'),

        'Table'[Activity_ID] = MAX('Table'[Activity_ID])

            && 'Table'[Days] = _maxDays

    )

VAR _chosenRow =

    MINX(

        ADDCOLUMNS(

            _candidateRows,

            "@key",

            CONCATENATE(

                'Table'[Activity_ID],

                'Table'[Days] & 'Table'[Counterparty]

            )

        ),

        [@key]

    )

VAR _thisRowKey =

    CONCATENATE(

        'Table'[Activity_ID],

        'Table'[Days] & 'Table'[Counterparty]

    )

RETURN

IF(_thisRowKey = _chosenRow, 1, 0)

If my response as resolved your issue, please accept it as a solution and please give kudos. 

Thanks,

Dinesh

[Zanqueta]

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.

[Zanqueta]

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]

Hi @Zanqueta , 

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

[Zanqueta]

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
    )

[Anonymous]

Hi @Amudha_Kumaran ,

Please refer below DAX measure.

Is Latest Flag =

VAR _maxDays =

    CALCULATE (

        MAX ( 'Table'[Days] ),

        ALLEXCEPT ( 'Table', 'Table'[Activity_ID] )

    )

VAR _randomPick =

    MINX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[Activity_ID] = MAX ( 'Table'[Activity_ID] )

                && 'Table'[Days] = _maxDays

        ),

        'Table'[Counterparty]

    )

RETURN

IF (

    'Table'[Days] = _maxDays

        && 'Table'[Counterparty] = _randomPick,

    1,

    0

)

If my response as resolved your issue, please accept it as a solution and please give kudos. 

Thanks,

Dinesh