Forum Discussion
How to make filter counts match distinct values
I've created a visualization with a couple of graphs and a table, along with a series of filters, that are based on a SQL Server view. The name of the view is Visit, with each visit having a VisitID. There is a table called Category, and each visit can have multiple categories. I pass these categories to the view in pipe-delimited format, and use Data Transformation to split those values into rows. Now, my category filter has the correct counts, and my table had the right rows, but the counts in the graphs and in the other filters were incorrect - it was counting the values for each VisitID/Category combo, rather than each VisitID. I was able to fix the graphs by creating a new measure with COUNTDISTINCT, but the filters still are wrong. How can I make the other filters count the distinct number of visits, rather than the combination of VisitID/Category?
Thank you!
5 Replies
- jaideepnema
Solution Sage
Can you please share a sample file if possible to check this ?
Unfortunately, the data is confidential. I'm hoping that the general scenario is clear, but if not, please let me know and I can probably mock something up.
I went ahead and created a sample. Here's a link to the PBIX. You'll notice that there are a total of 10 visits, but that some of those visits have multiple report categories. These reflect correctly on the pie chart and table - the pie chart is correct because I use a measure to DISTINCTCOUNT the VisitID. However, if you look at the Filters, you can see the problem. The Report Categories are correct, because if you select them, you get that number of items in the pie chart. However, you'll see that the Market Segment filters are incorrect - for example, it says that there are 55 Digital Marketing visits, but if you filter by that value, you get 7 results (which is correct).
v-yingjlCommunity Support
Hi jimforjim ,
Based on your description, you want the page filter count should be the same as pie visual count right?
If so, I'm afraid you could not acheive this in power bi currently because the count of each field in the level filters is based on the whole data table by default and we could not change it. In addition, even if we use measures, they could only be put in visual filters, page filters does not allow measures.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Unfortunately, the data is confidential. I'm hoping that this is a known pattern and a problem that has a known resolution, but if not, I may be able to mock up something.
