topic Re: Distinct Count for Item = 0 and Alt Item >0 in DAX Commands and Tips

Distinct Count for Item = 0 and Alt Item >0

brockry1 — Tue, 18 Mar 2025 21:03:54 GMT

I guess this is challenging because to date I have not found anyone that has solved this.

I am looking to find a count of stores that are not carrying one of our main items but are carrying a group of alternative items that have been grouped in the data table. In the attachment I am showing the store then all the items then a column that specices if it is a "Blockbuster SKU or an Alt SKU". Again I'm trying to figure out a count of the stores not carrying the "Blockbuser SKU" but carrying the "Alt SKU"

Not sure if this helps but this would be an example screen shot of the Items that fall with in the Alt SKU 483866. So in this example I would need stores not carrying Item Code "483866 but carrying the "Alt SKU 483866" and you can see which itms fall in this filter. I am ok with trying to build one dax command that solves for just one item and I can adjust the remaining dax commands for each blockbuster item.

Thanks.

Re: Distinct Count for Item = 0 and Alt Item >0

Deku — Tue, 18 Mar 2025 21:44:44 GMT

What is the expected output, can you give a example

Re: Distinct Count for Item = 0 and Alt Item >0

lbendlin — Tue, 18 Mar 2025 21:45:25 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Distinct Count for Item = 0 and Alt Item >0

brockry1 — Wed, 19 Mar 2025 14:20:59 GMT

What I'm trying to figure out is if we have a situation where I have a store that is not carrying the "Main" SKU but carrying one of the "Alternative" SKUs. The challenge is in some cases we can have where the store is carrying both or neither. I have to examples below:

So here if the first column for "Clinics Carrying 483866 is 0 but the second column Clinics Carrying Alt SKU is 1 I want to bring back a measure that counts all those rows. I have the dax codes for each item being carried. Just now need to figure out a way to bring back the count of not carrying vs carrying.

I applied a filter to my example screen shot above to show the first column of all stores not carrying the main but carrying the alt and the total count is 33. So that means out of over 700 stores we have 33 stores that are not carrying the main but carrying an alt.

Hope this helps.

Re: Distinct Count for Item = 0 and Alt Item >0

brockry1 — Wed, 19 Mar 2025 14:32:06 GMT

I'll do my best to try again using the table.

Sell-To-Customer	Clinics Carrying 483866	Clinics Carrying Alt 483866
Store 1	1	1
Store 2	0	0
Store 3	1	1
Store 4	0	0
Store 5	1	0
Store 6	0	1
Store 7	1	1
Store 8	0	1
Store 9	0	0

I have the Dax measures for each of those columns for store carrying. I just need a way to get the count of the stores not carrying from the first column but carrying from the second column.

Re: Distinct Count for Item = 0 and Alt Item >0

Anonymous — Thu, 08 May 2025 11:33:06 GMT

Hi brockry1
Thank you for reaching out microsoft fabric community forum.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Distinct Count for Item = 0 and Alt Item >0

Anonymous — Wed, 14 May 2025 09:24:49 GMT

Hi brockry1
I wanted to check if you had the opportunity to review the information provided by lbendlin and Deku . Please feel free to contact us if you have any further questions. If their response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Re: Distinct Count for Item = 0 and Alt Item >0

Anonymous — Tue, 03 Jun 2025 10:41:24 GMT

Hi brockry1
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you