Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to Solution.
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] )
)
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] )
)
@Yaro , make sure these are not measures [ComplCrit Score %], [QA Customer Critical %],
do the calculation in group by
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |