topic Re: Im having troubles with my switch in DAX in DAX Commands and Tips

Im having troubles with my switch in DAX

Anonymous — Mon, 06 Dec 2021 12:36:39 GMT

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!

Re: Im having troubles with my switch in DAX

bcdobbs — Mon, 06 Dec 2021 12:56:41 GMT

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

Re: Im having troubles with my switch in DAX

Anonymous — Mon, 06 Dec 2021 14:31:08 GMT

Can you try nested If Else instead of Switch ?

Re: Im having troubles with my switch in DAX

AlexisOlson — Mon, 06 Dec 2021 21:21:07 GMT

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

Re: Im having troubles with my switch in DAX

Anonymous — Tue, 07 Dec 2021 14:17:50 GMT

Worked like a charm! Thank you!