topic Re: SUMMARIZECOLUMNS filter repetition in DAX Commands and Tips

SUMMARIZECOLUMNS filter repetition

Anonymous — Mon, 06 Mar 2023 14:42:45 GMT

Hello everyone

I'm trying to create a table based on the DAX below. I was wondering if there is a more efficient way to code the filters - for every new column I'm filtering by [Department] A,B and C and [Shift Status] 1,2 and 3. Each column has a unique filter ie. [Worked], either -1, 0 or 1.

Is there a way to do a 'first' filter on [Department] and [Shift Status] then take the result of that and apply the unique filters. In other words so I don't have to repeat the 'common' filters for each new column, and if I need to change the filter criteria at some point I only have to change them in one place in the code.

_NewTable =

SUMMARIZECOLUMNS

(

'CBW'[ID],

'CBW'[UnitID],

"ColumnA", CALCULATE(SUM('CBW'[Hours Worked]),

FILTER('CBW', 'CBW'[Worked] = -1 && 'CBW'[Department] IN {"A","B","C"} && 'CBW'[Shift Status] IN {"1","2","3"})),

"ColumnB", CALCULATE(SUM('CBW'[Hours Rostered]),

FILTER('CBW', 'CBW'[Worked] = 0 && 'CBW'[Department] IN {"A","B","C"} && NOT ('CBW'[Unit]) IN {"U1", "U2"} && 'CBW'[Shift Status] IN {"1","2","3"})),

"ColumnC", CALCULATE(SUM('CBW'[Hours Rostered]),

FILTER('CBW', 'CBW'[Worked] = 1 && 'CBW'[Department] IN {"A","B","C"} && NOT ('CBW'[Unit]) IN {"U1", "U2"} && 'CBW'[Shift Status] IN {"1","2","3"}))

)

Re: SUMMARIZECOLUMNS filter repetition

WinterMist — Mon, 06 Mar 2023 14:57:45 GMT

Anonymous

When repeating code within the same DAX segment, it is recommended to place the repeated code inside a variable and then call that variable as needed.

VAR CommonFilter =

FILTER(

'CBW',

'CBW'[Department] IN {"A","B","C"}

&& 'CBW'[Shift Status] IN {"1","2","3"}

)

Regards,

Nathan

Re: SUMMARIZECOLUMNS filter repetition

tamerj1 — Mon, 06 Mar 2023 15:21:21 GMT

Anonymous

Please try

NewTable =
SUMMARIZE (
FILTER (
'CBW',
'CBW'[Worked] = -1
&& 'CBW'[Department]
IN { "A", "B", "C" }
&& 'CBW'[Shift Status] IN { "1", "2", "3" }
),
'CBW'[ID],
'CBW'[UnitID],
"ColumnA", SUM ( 'CBW'[Hours Worked] ),
"ColumnB", SUM ( 'CBW'[Hours Rostered] ),
"ColumnC", SUM ( 'CBW'[Hours Rostered] )
)