Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
This made my day. It works!
THANK YOU!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |