SUMX measure with blank values

[jmitch3062]

Hello, I'm just trying to sum up some totals for some true/false fields. If true it is marked 'True' in spreadsheet other wise the field is blank.  I want to return this measure value on a clusterd column chart. If field1 is true and the rest are blank then I want to include that record into the total count. 

If there are true values the measure field works fine, it sums up the true values. If there are no true values then I get the display error on my visual .

Only_EXCLl = SUMX('Screen Failure',IF('Screen Failure'[Excl 1] = TRUE() && ISBLANK('Screen Failure'[Excl 2])  && ISBLANK('Screen Failure'[Excl 3])  && ISBLANK('Screen Failure'[Excl 4])  && ISBLANK('Screen Failure'[Excl 5])  && ISBLANK('Screen Failure'[Excl 6])  && ISBLANK('Screen Failure'[Excl 7])  && ISBLANK('Screen Failure'[Excl 8]) && ISBLANK('Screen Failure'[Excl 9])  && ISBLANK('Screen Failure'[Excl 10]) && ISBLANK('Screen Failure'[Excl 11])  && ISBLANK('Screen Failure'[Excl 12])  && ISBLANK('Screen Failure'[Excl 13])  && ISBLANK('Screen Failure'[Excl 14])  && ISBLANK('Screen Failure'[Excl 15])  && ISBLANK('Screen Failure'[Excl 16])  && ISBLANK('Screen Failure'[Excl 17])  && ISBLANK('Screen Failure'[Excl 18])  && ISBLANK('Screen Failure'[Excl 19])  && ISBLANK('Screen Failure'[Excl 20])  && ISBLANK('Screen Failure'[Excl 21])  && ISBLANK('Screen Failure'[Excl 22])  && ISBLANK('Screen Failure'[Excl 23])  && ISBLANK('Screen Failure'[Excl 23])  && ISBLANK('Screen Failure'[Excl 24])  && ISBLANK('Screen Failure'[Excl 25])  && ISBLANK('Screen Failure'[Excl 26])  && ISBLANK('Screen Failure'[Excl 27])  && ISBLANK('Screen Failure'[Excl 28])  && ISBLANK('Screen Failure'[Excl 29])  && ISBLANK('Screen Failure'[Excl 30])  && ISBLANK('Screen Failure'[Incl 1])  && ISBLANK('Screen Failure'[Incl 2])  && ISBLANK('Screen Failure'[Incl 3])  && ISBLANK('Screen Failure'[Incl 4])  && ISBLANK('Screen Failure'[Incl 5])  && ISBLANK('Screen Failure'[Incl 6])  && ISBLANK('Screen Failure'[Incl 7])  && ISBLANK('Screen Failure'[Incl 8])  && ISBLANK('Screen Failure'[Incl 9])  && ISBLANK('Screen Failure'[Incl 10])  && ISBLANK('Screen Failure'[Incl 11])   && ISBLANK('Screen Failure'[Incl 12])  && ISBLANK('Screen Failure'[Incl 13]) ,1,0))

Any help would be appreciated

[Ashish_Mathur]

Hi,

In the first place, why are there so many columns that you want to run an aggregation over?  Shouldn't you be using the "Unpivot Other Columns" feature of the Query Editor?

[hnguy71]

Hi @jmitch3062 

I believe the error says you're comparing a text against a binary. To quickly fix, the following should work:

Only_EXCLl = SUMX('Screen Failure',IF('Screen Failure'[Excl 1] = "TRUE" && ISBLANK('Screen Failure'[Excl 2])  && ISBLANK('Screen Failure'[Excl 3])  && ISBLANK('Screen Failure'[Excl 4])  && ISBLANK('Screen Failure'[Excl 5])  && ISBLANK('Screen Failure'[Excl 6])  && ISBLANK('Screen Failure'[Excl 7])  && ISBLANK('Screen Failure'[Excl 8]) && ISBLANK('Screen Failure'[Excl 9])  && ISBLANK('Screen Failure'[Excl 10]) && ISBLANK('Screen Failure'[Excl 11])  && ISBLANK('Screen Failure'[Excl 12])  && ISBLANK('Screen Failure'[Excl 13])  && ISBLANK('Screen Failure'[Excl 14])  && ISBLANK('Screen Failure'[Excl 15])  && ISBLANK('Screen Failure'[Excl 16])  && ISBLANK('Screen Failure'[Excl 17])  && ISBLANK('Screen Failure'[Excl 18])  && ISBLANK('Screen Failure'[Excl 19])  && ISBLANK('Screen Failure'[Excl 20])  && ISBLANK('Screen Failure'[Excl 21])  && ISBLANK('Screen Failure'[Excl 22])  && ISBLANK('Screen Failure'[Excl 23])  && ISBLANK('Screen Failure'[Excl 23])  && ISBLANK('Screen Failure'[Excl 24])  && ISBLANK('Screen Failure'[Excl 25])  && ISBLANK('Screen Failure'[Excl 26])  && ISBLANK('Screen Failure'[Excl 27])  && ISBLANK('Screen Failure'[Excl 28])  && ISBLANK('Screen Failure'[Excl 29])  && ISBLANK('Screen Failure'[Excl 30])  && ISBLANK('Screen Failure'[Incl 1])  && ISBLANK('Screen Failure'[Incl 2])  && ISBLANK('Screen Failure'[Incl 3])  && ISBLANK('Screen Failure'[Incl 4])  && ISBLANK('Screen Failure'[Incl 5])  && ISBLANK('Screen Failure'[Incl 6])  && ISBLANK('Screen Failure'[Incl 7])  && ISBLANK('Screen Failure'[Incl 8])  && ISBLANK('Screen Failure'[Incl 9])  && ISBLANK('Screen Failure'[Incl 10])  && ISBLANK('Screen Failure'[Incl 11])   && ISBLANK('Screen Failure'[Incl 12])  && ISBLANK('Screen Failure'[Incl 13]) ,1,0))

However, I do see that there's room for improvements on your model. Possibly you may need some ETL and pivot your data to make it easier for yourself and the model.