topic Re: GROUPBY, COUNTX and SUMX for several columns in DAX Commands and Tips

GROUPBY, COUNTX and SUMX for several columns

Yaro — Tue, 04 Apr 2023 08:08:13 GMT

Hi.

I need to summarize table after UNION - data is coming from multiple sources.
So I was suggested to use GROUPBY and it works great, but only for one summarization (COUNTX).

If I add another summarization - SUMX, it is not working anymore.
Can you please suggest? Code is below.
Thank you.

Consol = var Monitorings = SELECTCOLUMNS(QA, "Site", related('Global Master List'[Global Site]), "CalDate", related('Calendar'[Date].[Date]), "CallIDString", [Call ID String], "NonCrit Score", 0, "QA Customer Critical", 0, "QA BusinessCrit Score", 0, "ComplCrit Score", 0, "CallsForecasted", 0, "CallsOffered", 0, "CallsAnswered", 0, "CallsAnsweredSL", 0, "CallsAbandonedSL", 0) var Quality = SELECTCOLUMNS(QA, "Site", related('Global Master List'[Global Site]), "CalDate", related('Calendar'[Date].[Date]), "CallIDString", blank(), "NonCrit Score", [NonCrit Score %], "QA Customer Critical", [QA Customer Critical %] , "QA BusinessCrit Score",[QA Business Critical %] , "ComplCrit Score", [ComplCrit Score %], "CallsForecasted", 0, "CallsOffered", 0, "CallsAnswered", 0, "CallsAnsweredSL", 0, "CallsAbandonedSL", 0 ) var UnitedTable=UNION (Monitorings,Quality) var UnitedTable2=summarize(UnitedTable,[Site],[CalDate],[CallIDString]) return groupby(UnitedTable, [Site], [CalDate], "MonitoringsNum",COUNTX(CURRENTGROUP(),[CallIDString]), "ComplCriticalScore",sumx(CURRENTGROUP(),[ComplCrit Score %]) )

Last SUMX is trhrowing error - "THE CALCULATE function cant be used in expression argument for the GROUPBY clause"

Re: GROUPBY, COUNTX and SUMX for several columns

amitchandak — Tue, 04 Apr 2023 08:46:53 GMT

Yaro , make sure these are not measures [ComplCrit Score %], [QA Customer Critical %],

do the calculation in group by

Re: GROUPBY, COUNTX and SUMX for several columns

Yaro — Tue, 04 Apr 2023 09:05:14 GMT

hi amitchandak, sorry, can you please explain a bit more, I didnt get you

Re: GROUPBY, COUNTX and SUMX for several columns

tamerj1 — Tue, 04 Apr 2023 09:46:33 GMT

Hi Yaro
Please try

Consol = VAR Monitorings = SELECTCOLUMNS ( QA, "Site", RELATED ( 'Global Master List'[Global Site] ), "CalDate", RELATED ( 'Calendar'[Date].[Date] ), "CallIDString", [Call ID String], "NonCrit Score", 0, "QA Customer Critical", 0, "QA BusinessCrit Score", 0, "ComplCrit Score", 0, "CallsForecasted", 0, "CallsOffered", 0, "CallsAnswered", 0, "CallsAnsweredSL", 0, "CallsAbandonedSL", 0 ) VAR Quality = SELECTCOLUMNS ( QA, "Site", RELATED ( 'Global Master List'[Global Site] ), "CalDate", RELATED ( 'Calendar'[Date].[Date] ), "CallIDString", BLANK (), "NonCrit Score", [NonCrit Score %], "QA Customer Critical", [QA Customer Critical %], "QA BusinessCrit Score", [QA Business Critical %], "ComplCrit Score", [ComplCrit Score %], "CallsForecasted", 0, "CallsOffered", 0, "CallsAnswered", 0, "CallsAnsweredSL", 0, "CallsAbandonedSL", 0 ) VAR UnitedTable = UNION ( Monitorings, Quality ) VAR UnitedTable2 = GROUPBY ( UnitedTable, [Site], [CalDate], [CallIDString], "@ComplCrit Score", SUMX ( CURRENTGROUP (), [ComplCrit Score] ) ) RETURN GROUPBY ( UnitedTable, [Site], [CalDate], "MonitoringsNum", COUNTX ( CURRENTGROUP (), [CallIDString] ), "ComplCriticalScore", SUMX ( CURRENTGROUP (), [@ComplCrit Score] ) )