Forum Discussion

OlegV's avatar
OlegV
Helper III
2 years ago
Solved

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

 

 

 

Need Help
  • OlegV's avatar
    OlegV
    2 years ago

    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])
)
)

     

3 Replies

  • OlegV's avatar
    OlegV
    Helper III
    2 years ago

    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])
)
)

     

    • OlegV's avatar
      OlegV
      Helper III
      2 years ago

      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.