Evaluate duplicate IDs on another column

[greenawayr]

Hi, 

Hope this makes sense, but if it does, appreciate any help.

I have a table where I need to evaluate each set of duplicated ID's and output in a new column if that set meets a condtion.

Example

ID	Date	Output
1	17/10/2021	0
1	18/10/2021	0
1	16/10/2019	0
2	17/10/2021	1
2	10/10/2021	1

So the above shows my output column for ID 1 = 0 because all three rows for "1" have been evaluated and we found that 1 of the dates was in the past (it only needs 1 date to have "expired" for the output to be 0). ID 2 = 1 because the dates are all in the future.

Any idea how I can create a column like "Output" for this?

Hope I've  explained this well enough.

Thanks

[ryan_mayu · Accepted solution]

@greenawayr 

you can also try this

Column = if(CALCULATE(min('Sheet2'[Date]),ALLEXCEPT(Sheet2,Sheet2[ID]))<today(),0,1)

[greenawayr]

Thank you both for these suggestions. These both fulfil my requirements.

One thing I omitted though was that I need to do a count of the unique items. I was assuming I'd get answers back in M rather than DAX, and that would allow me to just remove the duplicates.

However, I can still do this with a separate measure in DAX so thanks.

I actually came up with a convaluted way to do it in Power Query without the use of M. Not an efficient way, so I will be replacing it with your suggestions, but I thought I'd add it ere for anyone who's scared of code.

I created a copy of the Query, and then used the Group By function, setting my "aggregate" column as a Min of the Date column. This meant the single row left by the Group By function gave me the lowest date in the collection. I then created a very basic custom column with an IF that checked whether this date was in the past or future. In the past = 0, future = 1.

I then used the Merge Query to merge my new query with the original based on the ID, extracting the Output column from the new table and that gave me my solution. I imagine this is a resource hungry way of doing it, but it's another solution also.

If someone can deliver a similar resource using M code that would be the perfect answer for me.

Thanks

[amitchandak]

@greenawayr , create a new column like

if(isblank(countx(filter(table, [ID] = earlier([ID]) && [Date] <today()),[Date])),1,0)