Forum Discussion
One column has unique values but comparing based on column with duplicate values
In PowerBI desktop, I am working on finding unique ID numbers that do not have production plans.
There are two different tables being referenced, one with production data by unique ID. The other table has different material numbers that make up one unique ID (same as other table). My goal is to find "Material ID" that do not have production data.
I used the below DAX
This returns a "1" for the "Unique ID" in MaterialTable when there is production data for that "Unique ID" in the ProductionTable.
The problem is that multiple "Unique IDs" use the same "Material ID". I am trying to find only the values that do not have a "1" correlated to any row of the "Material ID". The example below shows this visually.
"Unique ID" 234234 and "Unique ID" 313134 both use Material ID 123456 but only "Unique ID" 234234 has production planned. I need to isolate the Material IDs like 654321 that does not have Production Planned for ALL instances of that Material ID. Filtering by blanks does not work as the Unique ID 313134 row would give a blank for Material ID 123456 but this Material ID is being used.
I hope this makes sense and any help is appreciated!
2 Replies
- amitchandak
Super User
jhearn123 , Based on what I got try measure like
CALCULATE(DISTINCTCOUNT('ProductionTable'[Unique ID]),FILTER('ProductionTable', 'ProductionTable'[Material ID] = EARLIER('MaterialTable'[Material ID])))
amitchandak unfortunately that does not work as the 'ProductionTable' does not contain the value MaterialID. The relationship between the two tables is the UniqueID. But multiple UniqueIDs can use a single MaterialID, so need to eliminate MaterialIDs that have any UniqueIDs in 'ProductionTable'
