Forum Discussion
Conditional distinct counts
Thinking about this, there is only one possible status for Product and Store combo.
Product A in Store A is either (a) in stock, or (b) out of stock but not discontinued or (c) out of stock AND discontinued.
(I don't think a product can be both in stock and discontinued at the same time, that wouldn't make sense.)
So -- the problem with your data is that you have two statuses per Store/Product combo sometimes. You might see a 0 (for out of stock) and a -1 (for discontinued).
The key is to transform the table so you get one status for each Product and Store combination.
I like to do this kind of work in Power Query. Essentially, we get the Product Store combos that are Discontinued, merge that back in with the original table, and we remove the 0's if the product is discontinued. Basically, if the product is Discontinued (-1), we no longer want to *also* be marking it Out Of Stock (0).
Then we can use the measures like the one you have set up:
Discontinued Count = CALCULATE(DISTINCTCOUNT ('Cleaned Data'[Product]),'Cleaned Data'[Status]=-1)In Stock Count = CALCULATE(DISTINCTCOUNT ('Cleaned Data'[Product]),'Cleaned Data'[Status]=1)Out of Stock but not Discontinued Count = CALCULATE(DISTINCTCOUNT ('Cleaned Data'[Product]),'Cleaned Data'[Status]=0)I'm linking to the updated PBIX here - you can see how the data is transformed from Data to Cleaned Data and how we remove the Status=0 rows when we also have a Status=-1 row.
https://1drv.ms/u/s!Asnj1wbkvlaggfRhXuubdJuciFAJ8g?e=ksGamY
Thinking about this, there is only one possible status for Product and Store combo.
Product A in Store A is either (a) in stock, or (b) out of stock but not discontinued or (c) out of stock AND discontinued.
(I don't think a product can be both in stock and discontinued at the same time, that wouldn't make sense.)
So -- the problem with your data is that you have two statuses per Store/Product combo sometimes. You might see a 0 (for out of stock) and a -1 (for discontinued).
The key is to transform the table so you get one status for each Product and Store combination.
I like to do this kind of work in Power Query. Essentially, we get the Product Store combos that are Discontinued, merge that back in with the original table, and we remove the 0's if the product is discontinued. Basically, if the product is Discontinued (-1), we no longer want to *also* be marking it Out Of Stock (0).
Then we can use the measures like the one you have set up:
I'm linking to the updated PBIX here - you can see how the data is transformed from Data to Cleaned Data and how we remove the Status=0 rows when we also have a Status=-1 row.
https://1drv.ms/u/s!Asnj1wbkvlaggfRhXuubdJuciFAJ8g?e=ksGamY