SUMMARIZECOLUMNS filter repetition

[ledders2023]

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"}))

)

[tamerj1]

@ledders2023 

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] )
)

[WinterMist]

@ledders2023 

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