Forum Discussion
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?
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)
3 Replies
- Greg_Deckler
Community Champion
Perhaps:
New Clients =
CONCATENATEX(
SELECTCOLUMNS(
FILTER(
SUMMARIZE(
'Table',
[ClientNumber],
"Count",COUNTROWS('Table'),
"MaxYear",MAX('Table'[Year])
),
[MaxYear] = 2020 && [Count] = 1
),
"__Client",[ClientNumber],
),
[__Client],
","
)
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
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)
