locked
DAX – How to group fields inside a CALCULATETABLE function. RRS feed

  • Question

  • Hello Experts,

    I need some help from you guys to resolve a calculation in DAX. I wrote the following DAX statement to get some counts based on the dates passed.

     

    EVALUATE  (     CALCULATETABLE (         ADDCOLUMNS (             SUMMARIZE (                 CROSSJOIN ( VALUES ( DataDisclosureAudit[Initiating Name - Id] ), 'Date' ),                 DataDisclosureAudit[Initiating Name - Id],                 'Date'[Calendar Month Display]             ),             "MONTHLY_COUNT", CALCULATE (                 IF (                     [hlp_cal_is_crnt_row_in_max_yr_month] = 1                         && [hlp_calc_Current] > 0,                     1,                     IF (                         [hlp_cal_is_crnt_row_in_max_yr_month] = 0                             && [hlp_calc_Current] + [hlp_calc_1_Prev]                             + [hlp_calc_2_Prev]                             >= 3,                         1,                         BLANK ()                     )                 )             )         ),         DATESBETWEEN ( 'Date'[DateAltKey], DATE ( 2011, 1, 1 ), DATE ( 2011, 12, 1 ) )     ) )

    I further want to use the SUMMARIZE to group by based on DataDisclosureAudit[Initiating Name - Id] and 'Date'[Calendar Month Display] and then apply a SUM function to aggregate the total in MONTHLY_COUNT. I used the SUMMARIZE and ADDCOLUMNS functions but the calculation keeps on throwing “MONTHLY_COUNT” cannot be found.

    Any help would be appreciate.

    Thanks and best regards,

    Chandima 


    Chandima Lakmal Fonseka

    Thursday, April 28, 2016 11:42 PM

Answers

All replies