Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables: Tests and Results. In the data model, the two tables are connected by the key column and it is a one (Tests) to many (Results). relationship.
Tests:
key | Lot code |
1 | xxx |
2 | yyy |
3 | zzz |
Results :
key | test | condition |
1 | A | pass |
1 | B | pass |
2 | A | fail |
2 | D | pass |
2 | C | pass |
3 | B | fail |
3 | D | fail |
I want to produce the following table without merging the two tables:
Lot code | Final Condition |
xxx | pass |
yyy | fail |
zzz | fail |
The final table is produced by first getting the lot code and condition columns from the two tables. Then, If the lot code has any condition = fail, then the final condition of the lot will fail. In other words, for a lot to pass, the lot can only have pass conditions.
Lot code | condition | final condition |
xxx | pass | pass |
xxx | pass | pass |
yyy | fail | fail |
yyy | pass | fail |
yyy | pass | fail |
zzz | fail | fail |
zzz | fail | fail |
I'm thinking of creating a new measure or new column on Power BI Desktop. But totally clueless how to write this. I hope I explained everything clearly. Please let me know if I need to clarify anything!
Thank you in advance!
Solved! Go to Solution.
is this what you want?
Measure = if(COUNTROWS('Results')=CALCULATE(COUNTROWS(Results),FILTER(Results,Results[condition]="pass")),"pass","fail")
Proud to be a Super User!
is this what you want?
Measure = if(COUNTROWS('Results')=CALCULATE(COUNTROWS(Results),FILTER(Results,Results[condition]="pass")),"pass","fail")
Proud to be a Super User!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |