CALCULATE - DISTINCTCOUNT - ALL with text column

[huguest]

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...

[v-yuta-msft]

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

[huguest]

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.

[v-yuta-msft]

Hi huguest,

Could you share a pbix file with some dummy data for further analysis?

Regards,

Jimmy Tao

[huguest · Accepted 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.

[PattemManohar]

@huguest Please post the sample data (copiable format) and expected output.

[huguest]

Unfortunately, my data cannot be posted here due to the confidentiality of the information 😞