Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AmberKothman
Regular Visitor

Only New clients

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?

1 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)

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Perhaps:

 

New Clients = 

  CONCATENATEX(

    SELECTCOLUMNS(

      FILTER(

        SUMMARIZE(

          'Table',

          [ClientNumber],

          "Count",COUNTROWS('Table'),

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

        ),

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

      ),

      "__Client",[ClientNumber],

    ),

    [__Client],

    ","

  )



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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. 

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)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!