Count Distinct in visual issue

[DB2024]

Hi all, 

hope you can help me with this. 

I am trying to have a card visual where I want to count product numbers from a dataset, uploaded through a query. 

The issue is with the visual when I select "Count (Distinct)" and is couting the wrong number. I think the problem comes when I activate the relationship between two tables, and set the cross-filter direction to both. 

The relationship is set on many to one. 

The screenshot is the visual I am using, and it's wrong, if I turn off the relationship, it will count correctly, but then if I filter for example on the store number which is from the other table it wont filter the other visual. 

I hope it's clear the isse I am having, if anyone can help with this please. 

[Anonymous]

Hi @DB2024 ,

Could you please provide some screenshots of the table relationships? Also, how do you set the "Store Number" in the slicer? It would be better to provide pbix file.

Best regards,

Rimmon Li

[dharmendars007]

Hello @DB2024 

Please check your relationship is many-to-one, ensure that the cross-filtering is set to "Single" direction, from the "many" side (Product/DSKU) to the "one" side (Store Number). You can adjust this in the "Manage Relationships" dialog box in Power BI. Setting it to "Both" might be causing an over-count as it's trying to filter both ways.

Also create a measure that handles the distinct count more explicitly:

DistinctDSKU = CALCULATE(DISTINCTCOUNT(ProductTable[DSKU]), ALL(StoreTable))

If you want to preserve the filtering from Store Number but still get a correct count of distinct DSKU, you can use ALLExcept

DistinctDSKU = CALCULATE(DISTINCTCOUNT(ProductTable[DSKU]), ALLEXCEPT(ProductTable, ProductTable[DSKU]))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Joe_Barry]

Hi @DB2024 

I would recommend not having the relationship set to cross-filter direction to both, this can cause issues with numbers, due to the probability of one of the tables being filtered by another.

Thsi article may help you with setting up your data model https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

To help you further, please provide more info or a screenshot of the relationships in your model and what each table is.

Joe

[DB2024]

Hi Joe, 

thanks for your answer! 

I tried actually to set the cross filter on single, indeed the count works fine, but then the visual won't work when I filter for example on the store number. 

the dashboard I am trying to build is an overview of the manual orders raised by stores, The visual with the count, is the number of products that have a parameter set up, what I would like to see now is that when I filter on a store number the visual will count those products for that store from the new table uploaded (called MIN_MAX) 

this is the screenshot of the relationship. 

I hope it's clear apologies if not! 

[Joe_Barry]

Hi @DB2024 

Let's do some troubleshooting

• Is your date table marked as a date table?
• Set the Date Table relationship one to many from the Date Table. Please make sure that these columns are the same format example "Short Date"
• In the Store Dimension Table, is the relationship with Manual Order Query with the Service Centre ID in both tables?Any mis-match will result in the table not being able to be filtered.
• Do they have the same format? As in both columns are either Text or a Integer?
• Double check the relationships in all the tables with the above

If it still doesn't work, are you allowed to send me a copy of your file? I will take a look.

Cheers

Joe