Forum Discussion
Identifying duplicate IDs based on values against one or more ID
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
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" ) > 0Hope this helps!Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
3 Replies
- Mariusz
Community Champion
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" ) > 0Hope this helps!Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.This made my day. It works!
THANK YOU!
- Mariusz
