Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hello guys
I am trying to translate a nested sql query on power bi and keep the ability to use the first level fields as slicers
Here is the scenario
The raw data looks like this
customer id | order_number | country | amount | date |
A | 1 | FR | 100 | 01/01/2020 |
A | 1 | FR | 100 | 01/02/2020 |
A | 1 | FR | 100 | 01/03/2020 |
A | 2 | UK | 100 | 01/01/2020 |
A | 2 | UK | 100 | 01/02/2020 |
B | 3 | US | 100 | 01/02/2020 |
B | 3 | US | 100 | 01/03/2020 |
B | 4 | FR | 100 | 01/02/2020 |
B | 4 | FR | 100 | 01/03/2020 |
C | 5 | US | 100 | 01/02/2020 |
C | 5 | US | 100 | 01/03/2020 |
For my calculation i need to make a first group by and move from order_number to customer_id level and it should give this
customer id | date | first sum | RowNumber | is_new_customer |
A | 01/01/2020 | 200 | 1 | 1 |
A | 01/02/2020 | 200 | 2 | 0 |
A | 01/03/2020 | 100 | 3 | 0 |
B | 01/02/2020 | 200 | 1 | 1 |
B | 01/03/2020 | 200 | 2 | 0 |
C | 01/02/2020 | 100 | 1 | 1 |
C | 01/03/2020 | 100 | 2 | 0 |
I have also added a row number to help me count the new customer (where row_number = 1 )
And my final need should be like this
DATE | count new customer | total customer | second sum |
01/01/2020 | 1 | 1 | 200 |
01/02/2020 | 2 | 3 | 500 |
01/03/2020 | 0 | 3 | 400 |
The challenge here is to create these measures and be able to filter on the country
If I select "US" as a country I should have this result
DATE | count new customer | total customer | second sum |
01/01/2020 | 0 | 0 | 0 |
01/02/2020 | 2 | 2 | 200 |
01/03/2020 | 0 | 2 | 200 |
I tried with a calculate table, but it is static and I can't use the filters of the first level
I have tried to create a dynamic measure where I have built the last aggregation table to countx the new customers, but its not filtring on the row_number = 1, plus its a horible dax code, I am new on power bi and I dont even know if I am on the right track
here is the code
count new customer =
-- var v = filter(
VAR a =
ADDCOLUMNS (
SUMMARIZE (
ADDCOLUMNS (
GROUPBY (
'Fact flat';
'Fact flat'[account_id];
'Fact flat'[po_number];
'Fact flat'[the_month];
'Fact flat'[continuity];
'Fact flat'[mrr_transposed]
);
"continuity_new_customers"; IF ( 'Fact flat'[continuity] = 0; 1; 'Fact flat'[continuity] )
);
[account_id];
[the_month];
[continuity_new_customers];
"sum mrr trans"; SUM ( 'Fact flat'[mrr_transposed] )
);
"RowNumberEmbed"; RANKX (
FILTER (
GROUPBY ( 'Fact flat'; 'Fact flat'[account_id]; 'Fact flat'[the_month] );
'Fact flat'[account_id] = EARLIER ( 'Fact flat'[account_id] )
);
[the_month];
;
ASC
)
)
var b =
ADDCOLUMNS(
a;
"Previous_Value"; CALCULATE (
MAxX( SUMMARIZE (
GROUPBY (
'Fact flat';
'Fact flat'[account_id];
'Fact flat'[po_number];
'Fact flat'[the_month];
'Fact flat'[mrr_transposed]
);
[account_id];
[the_month];
"sum mrr trans"; SUM ( 'Fact flat'[mrr_transposed] )
)
; [sum mrr trans] )
; FILTER ( a; [account_id]= EARLIER([account_id]) && [the_month] < EARLIER([the_month]) && [RowNumberEmbed] = EARLIER([RowNumberEmbed] ) -1
))
)
var c =
filter(
ADDCOLUMNS(b;
"row_number_new_customer"; if([Previous_Value] <>0 ; [RowNumberEmbed];1))
; [RowNumberEmbed] = 1
)
return COUNTX(c;[account_id])
Can you please help me on this one please, this is the third week where i am stuck on this task
Thank you in advance for your help
Solved! Go to Solution.
Here is second sum. PBIX with all three measures is included as an attachment below.
second sum =
VAR __Date = MAX('Table'[date])
VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
VAR __Table =
SUMMARIZE(
FILTER(
ALL('Table'),
[country] IN __Country
),
[date],
"Sum",SUM('Table'[amount])
)
VAR __Sum = SUMX(FILTER(__Table,[date] = __Date),[Sum])
RETURN
IF(ISBLANK(__Sum),0,__Sum)
Here is second sum. PBIX with all three measures is included as an attachment below.
second sum =
VAR __Date = MAX('Table'[date])
VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
VAR __Table =
SUMMARIZE(
FILTER(
ALL('Table'),
[country] IN __Country
),
[date],
"Sum",SUM('Table'[amount])
)
VAR __Sum = SUMX(FILTER(__Table,[date] = __Date),[Sum])
RETURN
IF(ISBLANK(__Sum),0,__Sum)
Total customer:
total customer =
VAR __Date = MAX('Table'[date])
VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
VAR __Table =
SUMMARIZE(
FILTER(
ALL('Table'),
[country] IN __Country
),
[customer id],
"Date",MIN('Table'[date])
)
VAR __Count = COUNTROWS(FILTER(__Table,[Date]<=__Date))
RETURN
IF(ISBLANK(__Count),0,__Count)
Here is count new customer:
count new customer =
VAR __Date = MAX('Table'[date])
VAR __Country = SELECTCOLUMNS('Table',"Country",[country])
VAR __Table =
SUMMARIZE(
FILTER(
ALL('Table'),
[country] IN __Country
)
,[customer id],"Date",MIN('Table'[date])
)
VAR __Count = COUNTROWS(FILTER(__Table,[Date] = __Date))
RETURN
IF(ISBLANK(__Count),0,__Count)
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.