Forum Discussion
GBKYE2
4 years agoFrequent Visitor
Summing and Slicing multiple columns
Hello, I've been given some data to build a report and part of it involves summarizing some data across multiple columns. Example: Incident Root Cause 1 Root Cause 2 Root Cause 3 Root ...
- 4 years ago
GBKYE2 don't change to 0 🙂
Try this:RootSlicer = VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer]) VAR _result = SUMX( 'Table', If( 'Table'[Root Cause 1] in _slicer_values || 'Table'[Root Cause 2] in _slicer_values || 'Table'[Root Cause 3] in _slicer_values || 'Table'[Root Cause 4] in _slicer_values, 1, blank() ) ) RETURN _result
or this:RootSlicer = VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer]) VAR _result = SUMX( SUMMARIZE( 'Table' 'Table'[Incident], 'Table'[Root Cause 1], 'Table'[Root Cause 2], 'Table'[Root Cause 3], 'Table'[Root Cause 4] ), If( MIN('Table'[Root Cause 1]) in _slicer_values || MIN('Table'[Root Cause 2]) in _slicer_values || MIN('Table'[Root Cause 3]) in _slicer_values || MIN('Table'[Root Cause 4]) in _slicer_values, 1, blank() ) )
SpartaBI
Community Champion
4 years agoGBKYE2 don't change to 0 🙂
Try this:
RootSlicer =
VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer])
VAR _result =
SUMX(
'Table',
If(
'Table'[Root Cause 1] in _slicer_values
|| 'Table'[Root Cause 2] in _slicer_values
|| 'Table'[Root Cause 3] in _slicer_values
|| 'Table'[Root Cause 4] in _slicer_values,
1,
blank()
)
)
RETURN
_result
or this:
RootSlicer =
VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer])
VAR _result =
SUMX(
SUMMARIZE(
'Table'
'Table'[Incident],
'Table'[Root Cause 1],
'Table'[Root Cause 2],
'Table'[Root Cause 3],
'Table'[Root Cause 4]
),
If(
MIN('Table'[Root Cause 1]) in _slicer_values
|| MIN('Table'[Root Cause 2]) in _slicer_values
|| MIN('Table'[Root Cause 3]) in _slicer_values
|| MIN('Table'[Root Cause 4]) in _slicer_values,
1,
blank()
)
)GBKYE2
4 years agoFrequent Visitor
Perfect! Thank you!