cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
huguest Regular Visitor
Regular Visitor

CALCULATE - DISTINCTCOUNT - ALL with text column

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

1 ACCEPTED SOLUTION

Accepted Solutions
huguest Regular Visitor
Regular Visitor

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

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.

6 REPLIES 6
Super User
Super User

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





huguest Regular Visitor
Regular Visitor

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

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

Community Support Team
Community Support Team

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

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

Highlighted
huguest Regular Visitor
Regular Visitor

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

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.

Community Support Team
Community Support Team

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

Hi huguest,

 

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

 

Regards,

Jimmy Tao

huguest Regular Visitor
Regular Visitor

Re: CALCULATE - DISTINCTCOUNT - ALL with text column

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.