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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Dynamic aggregation and calculation

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 idorder_numbercountryamountdate
1FR10001/01/2020
1FR10001/02/2020
1FR10001/03/2020
2UK10001/01/2020
2UK10001/02/2020
B3US10001/02/2020
B3US10001/03/2020
B4FR10001/02/2020
B4FR10001/03/2020
C5US10001/02/2020
C5US10001/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 iddatefirst sum RowNumberis_new_customer
A01/01/202020011
A01/02/202020020
A01/03/202010030
B01/02/202020011
B01/03/202020020
C01/02/202010011
C01/03/202010020

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

DATEcount new customertotal customersecond sum
01/01/202011200
01/02/202023500
01/03/202003400

 

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

DATEcount new customertotal customersecond sum
01/01/2020000
01/02/202022200
01/03/202002200

 

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

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

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)

 



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Community Champion
Community Champion

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)

 



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thx a lot @Greg_Deckler  !!! 😄

Happy to help! 🙂


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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

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)


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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

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)

 

 



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!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.