## Desktop

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

# Distinct Count by Coulmn

Hi,

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
Super User
Posts: 1,627
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.

All Replies
Super User
Posts: 1,627
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: 754
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..

Super User
Posts: 1,627
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]))`

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

Regards,

Simon Hou

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

## Re: Distinct Count by Coulmn

Thanks It worked :-)