Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi.
Please help me with following.
I have multiple tables (2 as example) from direct Power BI query - I need to consolidate them.
So my idea is to select data I need, UNION it and then summarize it by selected dimensions.
It works good with SUM, but I need to DISTINCTCOUNT one column and it doesnt accept it as reference.
I think it is taking it as measure, please suggest how to fix it?
Thank you!
Consolidated =
var Calls =
SELECTCOLUMNS(CallsRawSharePoint,
"Site", "Global",
"CalDate", related('Calendar'[Date].[Date]),
"CallIDString", 0,
"CallsForecasted", 0,
"CallsOffered", sum(CallsRawSharePoint[Total Offered Calls]),
"CallsAnswered", sum(CallsRawSharePoint[Total Answered Calls]),
"CallsAnsweredSL", sum(CallsRawSharePoint[Answered In SL]),
"CallsAbandonedSL", sum(CallsRawSharePoint[Abandoned In SL]),
"NonCrit Score", 0,
"QA Customer Critical", 0,
"QA BusinessCrit Score", 0,
"ComplCrit Score", 0
)
var Quality =
SELECTCOLUMNS(QA,
"Site", related('Global Master List'[Global Site]),
"CalDate", related('Calendar'[Date].[Date]),
"CallIDString", [Call ID String],
"CallsForecasted", 0,
"CallsOffered", 0,
"CallsAnswered", 0,
"CallsAnsweredSL", 0,
"CallsAbandonedSL", 0,
"NonCrit Score", [NonCrit Score %],
"QA Customer Critical", [QA Customer Critical %] ,
"QA BusinessCrit Score",[QA Business Critical %] ,
"ComplCrit Score", [ComplCrit Score %]
)
var UnitedTable = union(Calls,Quality)
return SUMMARIZE(UnitedTable,
[Site],
[CalDate],
"CallsOffered", sum([CallsOffered]),
"CallsAnswered" sum([CallsAnswered]),
"Monitorings Num", DISTINCTCOUNT(CallIDString)
)
Last row doesnt work.
Solved! Go to Solution.
@Yaro , Try group by in this case
Check the Dax solution
https://medium.com/@amitchandak/power-bi-power-query-vs-dax-append-and-summarize-data-233f173d0839
@Yaro , Try group by in this case
Check the Dax solution
https://medium.com/@amitchandak/power-bi-power-query-vs-dax-append-and-summarize-data-233f173d0839
hi @amitchandak
got stuck again
it works like a charm when there is one measure to be summarized
but when I am adding additional column to be summarized it gives me an error
return groupby(UnitedTable2, [Site],
[CalDate],
"MonitoringsNum",COUNTX(CURRENTGROUP(),[CallIDString]),
"ComplCriticalScore",SUMX(CURRENTGROUP(),[ComplCrit Score %])
)
It workins perfectly fine with COUNTX but SUMX gives an error
"The CALCULATE function cannot be used in an expression argument for the GROUPBY function"
Please suggest.
Thank you
thank you @amitchandak
I need distinctcount, so it worked with a bit of workaround
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |