Forum Discussion
Count Distinct in visual issue
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.
5 Replies
Hi Anonymous
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
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!
Hi Anonymous
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
Hello Anonymous
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
