Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
AZL
Frequent Visitor

Sum the resultant values from Distinctcount function.

Hey All,

 

I deadly need your helps guys.

 

Let's say I have the following table:

 

Capture.JPG

 

I want firstly to distinct count the values in Col1 based on Col2 to get:

 

A1>>>2

B1>>>1

C1>>>1

 

afterthat I want to count the resultant values based on Col3:

 

DD>>>2

BB>>>2

 

if I will use directly distinct count based on Col3 the result for "BB" will be 1 which is not what I want to get at the end.

 

Thank you all in advance.

 

Regards,

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @AZL

 

How about a DISTINCTCOUNT on the concatenation of the columns you are interested in? For instance, in your first example with Col1 and Col2, you create an additional calculated column

Table1[NewColum]=Table1[Col] & Table1[Col2]

and then

DISTINCTCOUNT(Table1[NewColum])

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @AZL

Assume you have the result as below

4.png

Per your requirement, you could create a measure

Measure = CALCULATE(DISTINCTCOUNT(Sheet3[col1]),ALLEXCEPT(Sheet3,Sheet3[col2]))

5.png

 

 

Best Regards

Maggie

AlB
Community Champion
Community Champion

Hi @AZL

 

How about a DISTINCTCOUNT on the concatenation of the columns you are interested in? For instance, in your first example with Col1 and Col2, you create an additional calculated column

Table1[NewColum]=Table1[Col] & Table1[Col2]

and then

DISTINCTCOUNT(Table1[NewColum])

AZL
Frequent Visitor

Hey @AlB

 

That works like heaven to me!!

Thank you so much for your smart solution.

AlB
Community Champion
Community Champion

@AZL

Smiley Happy you're very welcome. glad it helped.

Please mark it as solution then, so that it's available for others too.

Cheers 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.