Forum Discussion

CowsHateMilk's avatar
CowsHateMilk
Frequent Visitor
2 years ago
Solved

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:

VendorDistinct Count of TransactionID (Number of times Vendor has provided fruit + quality)
Vend22
Vend32


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:

BuyerVendorDateItemQualityTransactionID
BuyAVend101-01-22OrangeMedium1
BuyAVend201-01-22OrangeMedium1
BuyAVend301-01-22OrangeMedium1
BuyBVend101-02-22AppleGood2
BuyBVend401-02-22AppleGood2
BuyCVend101-02-23OrangeBad3
BuyCVend201-02-23OrangeBad3
BuyCVend301-02-23OrangeBad3
BuyCVend401-02-23OrangeBad3
BuyCVend501-02-23OrangeBad3
BuyDVend201-03-23OrangeMedium4
BuyDVend301-03-23OrangeMedium4
BuyDVend401-03-23OrangeMedium

4

 

4 Replies