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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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],

    ","

  )


@ 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!:
The Definitive Guide to Power Query (M)

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.