Forum Discussion
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
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.
6 Replies
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])
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.
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
