Forum Discussion

Yaro's avatar
Yaro
Frequent Visitor
3 years ago
Solved

Summarize doesnt work on var table

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.

3 Replies

    • Yaro's avatar
      Yaro
      Frequent Visitor

      thank you amitchandak 
      I need distinctcount, so it worked with a bit of workaround

    • Yaro's avatar
      Yaro
      Frequent Visitor

      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