DAX count / SUM with IF expressions

[decarsul]

Hi there!

Recently i've been tasked to switch some dashboards over to PowerBI (not desktop), from excel. This means i have to work with DAX, which i've not worked with much. So i'm hoping you all can help me!

The case:

I have a data set which is an external export of a question form.

These questions can be answered with Yes, No and N/A. And are saved as varchar. 

The value NO needs to be summed up to a total, but not for the entire row, only for specific columns within the row.

What i know i can do:

I can replace all NO value's for the specific columns to 1's and simply do a sum or count on them. (this will be negative for other calculated columns)

I can create a integer column copy of each of the source columns, and create a sum on them. Using the If statements below. (this would create inefficiency to my knowledge)

What i'm looking to do:

Create a nested 'count.if' within dax. I have already determined that the IF statement gives the proper result. This is also how i would create the extra columns.

Data sample:

IF('Form'[50104]="NO";1;0)
IF('Form'[50107]="NO";1;0)
IF('Form'[50301]="NO";1;0)
IF('Form'[50305]="NO";1;0)
IF('Form'[50401]="NO";1;0)

Any help would be appreciated!

[v-shex-msft]

Hi @decarsul ,

I'd like to suggest you to enter to query editor and enable 'unpivoted columns' on your fields, then you can write simple if statement formulas to check all fields.

Unpivot columns (Power Query)

Regards,

Xiaoxin Sheng

[decarsul · Accepted solution]

Update:

So after 2 days and a fresh cup of coffee, i figured out i simply made a typo in the column header names.

Now its just a string of if statements with a + in between. This works.

But there is probably a more efficient way?