Forum Discussion
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 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 |
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 grainIf 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.
12 Replies
- ThxAlot
Super User
Easy enough,
If you were able to wrap your head around this measure, DAX is almost no secret for you.
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
- 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.- 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 )
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
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.
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
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-echaithraCommunity Support
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 grainIf 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.
- 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.
