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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
Super User
Super User

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
Super User
Super User

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.

@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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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