Summing and Slicing multiple columns

[GBKYE2]

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 Cause 4
1	Human	System
2	Human
3	Human	Process
4	Human	Process
5	Process
6	System
7	Human	Process	System
8	External
9	Human	System
10	Human	Process

I created a Slicer table using:

SlicerTable = 
Distinct(
    UNION(
        VALUES('Table'[Root Cause 1]),
        VALUES('Table'[Root Cause 2]),
        VALUES('Table'[Root Cause 3]),
        VALUES('Table'[Root Cause 4])
    )
)

And a measure:

RootSlicer = 
If(
    MIN('Table'[Root Cause 1]) in VALUES(SlicerTable[RootCauseSlicer])
    || MIN('Table'[Root Cause 2]) in VALUES(SlicerTable[RootCauseSlicer])
    || MIN('Table'[Root Cause 3]) in VALUES(SlicerTable[RootCauseSlicer])
    || MIN('Table'[Root Cause 4]) in VALUES(SlicerTable[RootCauseSlicer]),
    1,
    blank()
    )

This works for filtering the table visual:

As you can see the "Count of incident" is 10 but I would like it to count the matching results and return "4" as the total. I would also like to build visuals/charts with some other columns in the original data such as breakdown by Team so is there any way to work around that?

Any suggestions would be greatly appreciated, thanks!

[SpartaBI]

@GBKYE2 try this: (I also created a VAR for the slicer values as a best practice for performance and readabilty. Perfornance wise probably not relevant in the size of your data, but good getting used to use VARs where possible):

RootSlicer = 
VAR _slicer_values = VALUES(SlicerTable[RootCauseSlicer])
VAR _result = 
SUMX(
	VALUES('Table'[Incident]),
	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()
    )
)
RETURN
	_result

[GBKYE2]

@SpartaBI Thanks for the input and the tip about VARs, I will keep that in mind in future!

I think I understand what that DAX is supposed to do, but when I implemented it I ran into some issues:

I thought this might be caused by the use of blank() so I tried replacing it with 0 for the purpose of the SumX but then this happens:

I can fix the table with a visual level filter with "Rootslicer = 1" but there's still some issues with the actual summations:

Any ideas what causes this? Thanks!

[SpartaBI · Accepted solution]

@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()
    )
)

[GBKYE2]

Perfect! Thank you!