cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
huguest Member
Member

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

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.

View solution in original post

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

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

huguest Member
Member

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

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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 126 members 1,437 guests
Please welcome our newest community members: