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
Anonymous
5 years agoNot applicable
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