Forum Discussion

kbruce's avatar
kbruce
New Member
5 years ago
Solved

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...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 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

kbruce's avatar
kbruce
New Member
5 years ago

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.

kbruce's avatar
kbruce
New Member
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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