Forum Discussion
DAX EXCEPT() with Filters
Hi all,
I've very recently gotten into powerbi. I'm trying to find the rows of a table that don't already exist in another, based on some slicers that will be on the dashboard.
I have a database with Buyers, Vendors, as well as some transaction info. A Buyer will only buy one type and quality of fruit, whereas Vendors may sell a variety of fruits and qualities. I essentially want to answer the question, "As a Buyer, which Vendor that you have not worked with before most likely be carrying your fruit + quality of choice?"
I've attached sample data as well as screenshots. Based on the screenshots, my desired output would be an except run between the two tables, and return the following:
| Vendor | Distinct Count of TransactionID (Number of times Vendor has provided fruit + quality) |
| Vend2 | 2 |
| Vend3 | 2 |
Link to download sample pbix file
https://drive.google.com/file/d/1vdPnu8_HTX4tMEHTK8YawX6e7G3_C5QP/view?usp=sharing
Thanks in advance for the help!
Sample Data:
| Buyer | Vendor | Date | Item | Quality | TransactionID |
| BuyA | Vend1 | 01-01-22 | Orange | Medium | 1 |
| BuyA | Vend2 | 01-01-22 | Orange | Medium | 1 |
| BuyA | Vend3 | 01-01-22 | Orange | Medium | 1 |
| BuyB | Vend1 | 01-02-22 | Apple | Good | 2 |
| BuyB | Vend4 | 01-02-22 | Apple | Good | 2 |
| BuyC | Vend1 | 01-02-23 | Orange | Bad | 3 |
| BuyC | Vend2 | 01-02-23 | Orange | Bad | 3 |
| BuyC | Vend3 | 01-02-23 | Orange | Bad | 3 |
| BuyC | Vend4 | 01-02-23 | Orange | Bad | 3 |
| BuyC | Vend5 | 01-02-23 | Orange | Bad | 3 |
| BuyD | Vend2 | 01-03-23 | Orange | Medium | 4 |
| BuyD | Vend3 | 01-03-23 | Orange | Medium | 4 |
| BuyD | Vend4 | 01-03-23 | Orange | Medium | 4 |
https://drive.google.com/file/d/19N_u1Zt1yaDHLWai2fLBbrmRLtuP_RZT/view?usp=sharing
let me know if this works for you .
best regards
4 Replies
- Daniel29195
Community Champion
https://drive.google.com/file/d/19N_u1Zt1yaDHLWai2fLBbrmRLtuP_RZT/view?usp=sharing
let me know if this works for you .
best regards
- CowsHateMilkFrequent Visitor
Hi there,
Apologies for the late response. Greatly appreciate your help here!
- Daniel29195
Community Champion
- CowsHateMilkFrequent Visitor
Apologies I've updated the link. Thank you!