Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have the following table:
Name | Year |
Customer A | 2022 |
Customer A | 2021 |
Customer A | 2021 |
Customer B | 2022 |
Customer B | 2020 |
Customer C | 2021 |
What I want: for each row, identify if that customer is a new client (i.e. didn't have any entries on previous years). Expected output (using a custom column, don't know if there are better approaches):
Name | Year | New? |
Customer A | 2022 | No |
Customer A | 2021 | Yes |
Customer A | 2021 | Yes |
Customer B | 2022 | No |
Customer B | 2020 | Yes |
Customer C | 2021 | Yes |
I already tried COUNT and FILTER methods combined, without much success. Any sugestions?
Thanks!
Solved! Go to Solution.
Hi @MarlonPereira ,
Please try:
New? =
var _a = CALCULATE(COUNT('Table'[Name]),FILTER('Table',[Name]=EARLIER('Table'[Name])&&[Year]<EARLIER('Table'[Year])))
return IF(_a>=1,"No","Yes")
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MarlonPereira ,
Please try:
New? =
var _a = CALCULATE(COUNT('Table'[Name]),FILTER('Table',[Name]=EARLIER('Table'[Name])&&[Year]<EARLIER('Table'[Year])))
return IF(_a>=1,"No","Yes")
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Perfect, my previous attempt didn't include the EARLIER function. Thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |