Only New clients

[AmberKothman]

Hello, 

I am new to PowerBi and Dax, so the formula's are still difficult, but hopefully someone can help me. 

I need to find only the new cliënts for 2020, that do not occur in any other year. 

The table looks like this:

Clientumber:

1        2018

1        2019

1        2020

2        2017

2        2020

3        2019

4        2020

5         2018

5         2020

So if this was my data, i need only client nr. 4 because that one has no other year. How can i do this for a large data set?

[Greg_Deckler]

Perhaps:

New Clients = 

  CONCATENATEX(

    SELECTCOLUMNS(

      FILTER(

        SUMMARIZE(

          'Table',

          [ClientNumber],

          "Count",COUNTROWS('Table'),

          "MaxYear",MAX('Table'[Year])

        ),

       [MaxYear] = 2020 && [Count] = 1

      ),

      "__Client",[ClientNumber],

    ),

    [__Client],

    ","

  )

[AmberKothman]

I think i filled in the wrong things, because all i get is Error as an outcome. 

Is there a simple start, or a way that i can do this in multiple staps? Because this is to complex at once for me.  Even after a powerBi course the start is hard. 

[HotChilli · Accepted solution]

Create a measure:

CountOfClientEntries = CALCULATE(COUNT(TableT[ClientNumber]), ALLEXCEPT(TableT, TableT[ClientNumber]))

(substitute your table and column names).

Drag the measure, clientnumber and year on to a table in Report view.

Using the Filter pane, filter CountOfClientEntries to be 1 (assuming there will only be 1 entry for each year at most).

Also, filter the Year to be 2020.

Your table should now show only entries that have a year of 2020 (and that entry is the only entry for that client)