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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
OlegV
Helper III
Helper III

cannot create a calculated column with groupby, summarize, summarizecolumns

Hello,

I am stuck with the code, please help me.

 

I am createing a calculated table using variables. At a certain step, I need to group leftjoined tables and create a calculated column "ActualDeliveryDate". The aim is populate the "ActuralDeliveryDate" column with the maximum delivery date for each SalesOrder, but only if there are no blank delivery dates for that SalesOrder. If there are any blank delivery dates, it replaces them with BLANK() using this code

 

 

 

 

 

"ActuralDeliveryDate",if(COUNTROWS(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder]) && ISBLANK([ActualDeliveryDate]))) > 0, BLANK(),MAXX(CURRENTGROUP(),[ActualDeliveryDate])

 

 

 

 

 

 

 

1) when I use a groupby function, it does not work,
2) when I use summarise function, it works, but this code does not sum correctly.

 

 

 

"TotalQty", CALCULATE(SUMX(join_tab,[Quantity]))

 

 

 

 

 SUMX is summing up all the rows in the join_tab, not considering the context in which it's being called.

3) when I use summarisecolumns function, it does not work, because group_colums are not qualified

I really do no know what to do. Please help me.


HERE IS A LINK TO A SAMPLE PBI FILE on my onedrive
sample.pbix

 

 

 

1 ACCEPTED SOLUTION
OlegV
Helper III
Helper III

Here is a working solution:

 

var group_tab =
 ADDCOLUMNS(
SUMMARIZE(
    join_tab,
    [SalesOrder],
    [OrderDate]),
    "TotalQty", SUMX(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder])),[Quantity]),
    "ActuralDeliveryDate",if(COUNTROWS(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder]) && ISBLANK([ActualDeliveryDate]))) > 0, BLANK(),MAXX(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder]),[ActualDeliveryDate])
)
)

 

View solution in original post

3 REPLIES 3
OlegV
Helper III
Helper III

Here is a working solution:

 

var group_tab =
 ADDCOLUMNS(
SUMMARIZE(
    join_tab,
    [SalesOrder],
    [OrderDate]),
    "TotalQty", SUMX(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder])),[Quantity]),
    "ActuralDeliveryDate",if(COUNTROWS(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder]) && ISBLANK([ActualDeliveryDate]))) > 0, BLANK(),MAXX(FILTER(join_tab,[SalesOrder] = EARLIER([SalesOrder]),[ActualDeliveryDate])
)
)

 

AnalyticPulse
Super User
Super User

@OlegV 

Instead of trying to directly calculate the ActualDeliveryDate in a calculated table using variables, you might consider using measures to dynamically calculate it based on the context of your visuals.

can you provide sample data fo reference.

 

Learn Power BI free:

https://analyticpulse.blogspot.com

Learn Power BI free

DAX Functions

Powerbi Visualisation

@AnalyticPulse 

here is a link to sample pbi file 
sample.pbix

I will not use this calculate table in any visual in power bi desktop. I will paste it to Powerbi report builder, and create a report.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 NL Carousel

Fabric Community Update - February 2025

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