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.
I need a Not & Not with And filter
In this example I have 3 tables related to bicycles. Unit, UnitOption and Option.
Unit is related to UnitOption with UnitKey,
UnitOpion is related to Option with OptionKey.
In the first case the result set needs to be all of the units that do "NOT" have Option 12A - Front basket . The result set would be Unit 951. In the second case I want to see all the units that do "NOT" have option 12A Front Basket "AND" do not have option 35B Horn. The result set would then be Units 456 and 925. Neither of these units has both option 12A Front Basket and 35B Horn. I need to be able to filter "Not" and "Not" with "And" . Your help is much appreciated.
UnitTable | UnitOptionTable |
| OptionTable | |||
UnitKey | UnitKey | OptionKey | OptionTable | OptionName | ||
123 | 123 | 35B | 35B | Horn | ||
456 | 123 | 12A | 12A | Front Basket | ||
789 | 123 | 51G | 51G | Saddle Basket | ||
951 | 456 | 66F | 66F | Head Light | ||
456 | 12A | 12A | Front Basket | |||
456 | 77X | 77X | Tail Light | |||
789 | 35B | 35B | Horn | |||
789 | 21G | 21G | Sticker PKG | |||
789 | 12A | 12A | Front Basket | |||
951 | 89K | 89K | Grip Streamers | |||
951 | 5FR | 5FR | Fork Springs | |||
951 | 53F | 53F | Leather Seat |
Solved the first one with a variation of my Not in Common Quick Measure. https://community.powerbi.com/t5/Quick-Measures-Gallery/Not-In-Common/m-p/388273
NotInCommon = VAR tableA = ALL(Units) VAR tableB = CALCULATETABLE(Units) VAR results = EXCEPT(tableA,tableB) RETURN CONCATENATEX(results,[UnitKey],",")
See attached, Page 8.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |