Forum Discussion

GBKYE2's avatar
GBKYE2
Frequent Visitor
4 years ago
Solved

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:

 

IncidentRoot Cause 1Root Cause 2Root Cause 3Root Cause 4
1HumanSystem  
2Human   
3HumanProcess  
4HumanProcess  
5Process   
6System   
7HumanProcessSystem 
8External   
9HumanSystem  
10HumanProcess  

 

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!

Need Help
  • SpartaBI's avatar
    SpartaBI
    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()
    )
)

4 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    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's avatar
      GBKYE2
      Frequent Visitor
      4 years ago

      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's avatar
        SpartaBI
        Community Champion
        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()
    )
)