Reply
Frequent Visitor
Posts: 3
Registered: ‎12-26-2016
Accepted Solution

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

 


Accepted Solutions
Highlighted
Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: Distinct Count by Coulmn

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post


All Replies
Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: Distinct Count by Coulmn

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Posts: 756
Registered: ‎06-03-2016

Re: Distinct Count by Coulmn

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.

 

Frequent Visitor
Posts: 3
Registered: ‎12-26-2016

Re: Distinct Count by Coulmn

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

 

 

Highlighted
Super User
Posts: 1,631
Registered: ‎07-03-2015

Re: Distinct Count by Coulmn

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Moderator
Posts: 2,588
Registered: ‎03-06-2016

Re: Distinct Count by Coulmn

@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

Frequent Visitor
Posts: 3
Registered: ‎12-26-2016

Re: Distinct Count by Coulmn

Thanks It worked :-)