Identifying duplicate IDs based on values against one or more ID

[ScottieLobster]

Hello, 

I started using Power BI appox. 3 months ago on a "pushing buttons until it works" basis. 

I want to produce a column or table that shows only those client IDs that have never had a "Yes" value returned by a calculated column. The calculated column is based on whether a client has made an order before a particular date. 

Here is some example data:

ID number	Name	Yes or No (on or before 02/10/2017)	Order Date
1234	Mickey Mouse	Yes	02/10/2107
1234	Mickey Mouse	No	03/11/2017
5678	Jon Snow	Yes	01/01/2016
5678	Jon Snow	Yes	03/06/2017
5678	Jon Snow	No	01/11/2017
9876	Smurfette	Yes	01/09/2017
9876	Smurfette	No	01/12/2017
1920	Skeletor	Yes	01/08/2017
1920	Skeletor	No	01/01/2018
1920	Skeletor	No	01/06/2018
1920	Skeletor	No	01/02/2019
5647	Invader Zim	No	01/12/2018
5647	Invader Zim	No	01/06/2019
5647	Invader Zim	No	01/01/2019

In essence, I want to return only Invader Zim or put a "True/False" value only on Invader Zim, as everyone else has made an order before 02/102/2017 except him. 

I have one table (table1) with all orders made by all clients and another table (table2) with only orders made before 02/10/2017. I have also made a distinct value of all client IDs to relate these two tables together but that doesn't seem to have helped. If I try to use a "CONTAINS" function to exclude those IDs in table1, I get a circular error. 

Help me, Power BI Community - you're my only hope. 

Best, 

Lobster

[Mariusz · Accepted solution]

Hi @ScottieLobster 

You can add a column like below, it will return True if "Yes" and False if "No".

Column = 
CALCULATE( 
    COUNTROWS( 'Table' ), 
    ALLEXCEPT( 'Table', 'Table'[ID number] ),
    'Table'[Yes or No (on or before 02/10/2017)] = "Yes"
) > 0

Hope this helps!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[ScottieLobster]

This made my day.  It works!

THANK YOU!

[Mariusz]

Hi @ScottieLobster 

Always happy to help!

Best Regards,
Mariusz

Please feel free to connect with me.