topic Re: How to get Sum on Total Row for a measure in DirectQuery? in DAX Commands and Tips

How to get Sum on Total Row for a measure in DirectQuery?

hemanthk — Mon, 04 Apr 2022 15:56:58 GMT

Hi,

I have a measure to get the days between two dates and I want the total of this measure to be Sum, but it is giving me a MIN value on the total row of the Table Visual.

I am using DirectQuery.

My measure is like below:

1_WDays =

VAR BEGDATE = MIN('ZTAQ_CP03_V01'[Position Open (Posted) Date])

VAR ENDDATE = MIN('ZTAQ_CP03_V01'[Position Hired Date])

VAR HOLIDAYS = CALCULATE(DISTINCTCOUNT('BU Holidays'[Date]),FILTER('BU Holidays', 'BU Holidays'[Date] >= BEGDATE && 'BU Holidays'[Date] <= ENDDATE))

VAR WEEKENDS = CALCULATE(DISTINCTCOUNT(CAL[Date]),FILTER(CAL, CAL[Date] >= BEGDATE && CAL[Date] <= ENDDATE && WEEKDAY(CAL[Date], 2) > 5))

RETURN (DATEDIFF(BEGDATE, ENDDATE, DAY) - HOLIDAYS - WEEKENDS)

Can you please suggest how to get the Sum for this measure in DirectQuery.

Re: How to get Sum on Total Row for a measure in DirectQuery?

johnt75 — Mon, 04 Apr 2022 16:00:50 GMT

Rename your current measure to be "1_Wdays individual" then create a new measure

1_Wdays = IF( ISINSCOPE('Table'[Position number]), [1_Wdays individual], var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Position number]), "@value", [1_Wdays individual]) return SUM( summaryTable, [@value]) )

Re: How to get Sum on Total Row for a measure in DirectQuery?

hemanthk — Mon, 04 Apr 2022 19:18:19 GMT

Hi johnt75 ,

Thank you for your response.

I tried the code as below:

I am getting error: Too many arguments passed to SUM Function.

Can you please let me know what should be corrected?

Re: How to get Sum on Total Row for a measure in DirectQuery?

hemanthk — Tue, 05 Apr 2022 03:57:26 GMT

Hi,

I fixed it by creating a New Table instead of another measure and adding DAX: ADDCOLUMNS and SUMMARIZE functions to it.

Thanks, John for your input.