Forum Discussion
Re-Evaluating Minimum Based on Selection
ALLEXCEPT is a rather blunt tool. You may want to use REMOVEFILTERS instead.
How are you "filtering out certain sellers" ? Page level filter? Disconnected slicer?
Are you assuming that each seller is only selling products once on each day? What would happen if Seller A sold item AB123 once for 15 and once for 14 - on the same day ?
On a general note - not sure you are aware but your issue is anything but simple. It involves severe pointer gymnastics, going from a seller to the products they sold to the sellers who sold these products to the minimum to a comparison. No wonder this has you stumped for a while.
Cheapest =
var a = SUMMARIZE('Table',[Item],"mp",min('Table'[Price]))
var b = ADDCOLUMNS(a, "cmp",CALCULATE(min('Table'[Price]),REMOVEFILTERS('Table'[Seller])))
var c = filter(b,[cmp]=[mp])
return countrows(c)
Thanks again for your response, to answer your questions:
- I'm using a page level slicer to filter out the sellers
- I am fixing duplication issues in the SQL which loads the data into Power BI, there will only ever be one record for each Day/Item/Seller - though for clarification, there will be multiple days where the data could be different to the previous day, but I added the date field into the SUMMARIZE part to account for that.
I tried your suggestion, and put the code into a calculated measure, but unfortunately it did not have the desired results; before filtering it matched perfectly to the numbers which I was getting, though upon filtering the numbers do not change so I'm not sure the MIN is getting recalculated. I suspect it has something to do with the REMOVEFILTERS but I changed it to ALLEXCEPT, amongst some other things, and got the same results.
If you have any suggestions they would be appreciated.