Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
greenawayr
Helper I
Helper I

Evaluate duplicate IDs on another column

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

 

IDDateOutput
117/10/2021      0
118/10/2021      0
116/10/2019      0
217/10/2021      1
210/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

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@greenawayr 

you can also try this

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@greenawayr 

you can also try this

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Super User
Super User

@greenawayr , create a new column like

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.