March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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]))
Solved! Go to Solution.
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])
)
)
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])
)
)
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:
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |