Forum Discussion
kbruce
5 years agoNew Member
Obtain Unique values within distinct values
Hi Folks, I am trying to obtain the number of uninque values within a measure of distinct values. The goal is to display a stacked column that shows the total number of clients, the distinct numb...
- Anonymous5 years ago
Hi kbruce ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column to get the Year
Year = YEAR('Table'[Date])2. Create two measures as below to get the count of new clients
Measure = VAR _minyear = CALCULATE ( MIN ( 'Table'[Year] ), ALL ( 'Table'[Year] ) ) VAR _curyear = SELECTEDVALUE ( 'Table'[Year] ) VAR _preclients = CALCULATETABLE ( DISTINCT ( 'Table'[Client name] ), FILTER ( ALL ( 'Table' ), 'Table'[Year] < _curyear ) ) VAR _curclients = CALCULATETABLE ( DISTINCT ( 'Table'[Client name] ), FILTER ( 'Table', 'Table'[Year] = _curyear ) ) RETURN IF ( _curyear = _minyear, DISTINCTCOUNT ( 'Table'[Client name] ), COUNTROWS ( EXCEPT ( _curclients, _preclients ) ) )Count of new clients = SUMX ( VALUES ( 'Table'[Year] ), [Measure] )Best Regards