Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |