Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to get a distinct count of text values in a column using the following measure:
DistinctCount_All_Categories = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Type]="Sales",ALL('Table'[Category]))
If I don't have a "Category" selected, the formula works fine. If I select a Category (for other fields in my visual), the Distinct Count returns the wrong value.
The format of the "ID" field is text, and contains some text and numerical values (e.g. 18-0129, BSSD-022, 41154). It looks like my measure is returning the number of numerical values in thte ID field, ignoring those that are text...
Any help would be appreciated!
*UPDATE* Just realized that this was caused by a slicer that I added to the visual. I have several slicers on, but only one of them is causing this issue...
Solved! Go to Solution.
I cannot share the data due to confisentiality, and creating a dummy dataset would require too much effort at the moment. I have identified a workaround that I can use, so I will go with that option for now. Thank you for taking the time to respond.
Hi huguest,
Based on your description, your count result is not correct after filter, right? What does your table stucture and relationship look like? Try changing data type format to text and check if this issue persists.
Regards,
Jimmy Tao
You are correct, the count becomes inaccurate after I apply a filter (with a slicer). There is no relationship, all of the fields are coming form a single table. I already changed the format to text, without success.
Hi huguest,
Could you share a pbix file with some dummy data for further analysis?
Regards,
Jimmy Tao
I cannot share the data due to confisentiality, and creating a dummy dataset would require too much effort at the moment. I have identified a workaround that I can use, so I will go with that option for now. Thank you for taking the time to respond.
@huguest Please post the sample data (copiable format) and expected output.
Proud to be a PBI Community Champion
Unfortunately, my data cannot be posted here due to the confidentiality of the information 😞