Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jmitch3062
Frequent Visitor

SUMX measure with blank values

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 .

jmitch3062_0-1685142031987.png

 

 

 

 

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

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
hnguy71
Memorable Member
Memorable Member

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.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.