Im having troubles with my switch in DAX

[ajeeth20]

Hi Guys,

Im a beginner with DAX and what I dit wrong. This is my error message: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

And this is my code:

CountFalse = SWITCH(

IF(

FILTER(Table1,[CheckColumn2] = "NO"), COUNT(Table1[Column2]), BLANK()

),

IF(

FILTER(Table1,[CheckColumn9] = "NO"), COUNT(Table1[Column9]), BLANK()

),

IF(

FILTER(Table1,[CheckColumn15] = "NO"), COUNT(Table1[Column15]), BLANK()

),

IF(

FILTER(Table1,[CheckColumn18] = "NO"), COUNT(Table1[Column18]), BLANK()

)

)

I want this code to count all the NO's in Table1. Multiple Columns have a NO so Im using a switch to count them all. 

Does someone know how I can make this work or do this in a easier way?

Thanks!

[bcdobbs · Accepted solution]

Try something like

VAR Col2Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column2] = "NO"
		)
		
VAR Col9Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column9] = "NO"
		)
		
VAR Col15Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column15] = "NO"
		)
		
VAR Col18Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column18] = "NO"
		)
		
RETURN Col2Count + Col9Count + Col15Count + Col18Count

[AlexisOlson · Accepted solution]

The reason for the error is that FILTER returns a table and IF expects either True or False as its first argument, not a table.

@bcdobbs has the best solution but you could do it with an iterator too:

SUMX (
    Table1,
	IF ( Table1[CheckColumn2]  = "NO, 1 ) +
	IF ( Table1[CheckColumn9]  = "NO, 1 ) +
	IF ( Table1[CheckColumn15] = "NO, 1 ) +
	IF ( Table1[CheckColumn18] = "NO, 1 )
)

[ajeeth20]

Worked like a charm! Thank you!

[kp54357]

Can you try nested If Else instead of Switch ?

[bcdobbs · Accepted solution]

Try something like

VAR Col2Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column2] = "NO"
		)
		
VAR Col9Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column9] = "NO"
		)
		
VAR Col15Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column15] = "NO"
		)
		
VAR Col18Count = 
	CALCULATE (
		COUNTROWS( Table1 ),
		Table1[Column18] = "NO"
		)
		
RETURN Col2Count + Col9Count + Col15Count + Col18Count