Forum Discussion
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 number of clients for that year, recurring clients and the number of new clients for each year that did not appear in previous years.
I've been able to display 3 of the 4 items in a single graph, but I am unable to correctly calculate the NEW clients for each year that do not appear in previous years. I toyed around with "IF" statements, counta/x and tables with min dates but cannot figure out how to display this subset of data.
My idea has been on the focus on an "IF" statement that checks for previous instances if a unique client is being referenced in previous years.
The data I am using is very simple. Client Name and Date (with hierarchy).
Client name Date
A-client January 2000
A-client January 2010
A-client January 2021
B-Client January 2005
B-Client January 2015
B-Client January 2021
C-Client January 2021
D-Client January 2000
D-Client January 2005
D-Client January 2015
D-Client January 2021
The results should have counted each NEW client for each year by comparing the current date with previous dates.
2000 2005 2010 2015 2021
2 1 0 0 1
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
5 Replies
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
I'm almost there. I did not take into consideration multiple instances of a client in the same year, but Anonymous did. I believe that I am all set with both of these replies.
Upon further review/tinkering, Anonymous solution is not correct and does not solve the last part of what is needed.
Client A is listed multiple times in the same year FrankAT is closer, but the measure calculating the column adds a 1 next to each of the duplicates, below. I need to remove the duplicates to obtain an accurate count.A-client January 2000
A-client January 2000
A-client January 2000
Hi kbruce ,
As checked the table data in your first post, these three clients A-client are from different years(2000,2010 and 2021) separately. So the client A-client is not duplicate value in same year... Could you please provide some sample data with duplicated clients and your actual expected result with correct calculation logic and specific examples. Thank you.
Best Regards
