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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rkacom
Employee
Employee

Distinct Count by Coulmn

Hi,

 

Continuing to https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Count-by-column/td-p/28665

  

Term                ID

Spring 15          1

Spring 15          2

Spring 15          1

Spring 16          1

Spring 16          1

Spring 16          3

Spring 17          4

Spring 17          1

 

I would like to create additional column as follows. i.e count distict count for each term based on ID

 

Term                ID    DisCount

Spring 15          1     2

Spring 15          2     2

Spring 15          1     2

Spring 16          1     2

Spring 16          1     2

Spring 16          3     2

Spring 17          4     2

Spring 17          1     2

 

1 ACCEPTED SOLUTION

Just write the single measure I posted earlier, thne add a visual (say a table or matrix) and add Term and the measure. This will give you the distinct count by term. You need to change your thinking from the way it is done in Excel. You write a measure and then visualise the results. No need to store the results anywhere. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

6 REPLIES 6
v-sihou-msft
Employee
Employee

@rkacom

 

In this scenario, you can create a calculated column to distict count the IDs group by each Term.

 

Column = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALLEXCEPT('Table','Table'[Term]))

 

 

44.PNG

PS: In Power BI Desktop, it will aggregate values for duplicate rows when draging the calculated column into a table visual.

 

Regards,

Simon Hou

Baskar
Resident Rockstar
Resident Rockstar

Cool dude,

 

 

Create one calculated column 

 

Cal_Colum = Term & "-" & ID

 

It will give u the extra column with TERM-ID Combination , then u can find very easily.

 

MEASURE  = DISTINCTCOUNT ( Cal_Colum )

Hope it will help u to solve your prob, if not let me know i will help u dude.

 

I am not looking for cumulative. ..Cal_column will give the total as 6

 

I would like to get the count for each Term

 

I am okay with Measure too. It seems need to create measure for each Term(Pls correct me). As list of Term is huge it may not be possible manually to create that many measure..

 

 

Just write the single measure I posted earlier, thne add a visual (say a table or matrix) and add Term and the measure. This will give you the distinct count by term. You need to change your thinking from the way it is done in Excel. You write a measure and then visualise the results. No need to store the results anywhere. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks It worked 🙂

Why do you want this column of data?  Chances are this is not the best approach.  Read my article here http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

 

you can write a measure for distinct count as follows

 

measure = distinctcount(table[Column])



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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