Conditional distinct counts

[rk761651]

In a dataset I'm playing with at the moment I have columns [location], [product] and [status].

• Location is the store name
• Product is the product ID
• Status has a value of 1 (in stock), 0 (out of stock) and -1 (discontinued, for that location/product combination)

I would like to create a measure for a count of products that are in stock in any location (so even one row with a status of 1 counts as in stock), another measure for all products that are out of stock but not discontinued (where all rows for that productd ID are exactly 0) and then another for all products which have been discontinued in all locations.

The first is trivial, something like:

Measure = CALUCLATE ( DISTINCTCOUNT ( data[product]), data[status] = 1)

 And I can also see that if I can get one of the other, the third measure is trivial too (subtract both measures from a distinct count of the products).

My question is: is there a more elegant way of doing this? And if not, how can I calculate one of the others?

[viviank · Accepted solution]

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