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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

count blanks in row across multiple columns dependent on category

UPDATE:  Hi, I want to create the 'Missing Inputs' column counting the blanks for columns that should have data depending on the Status column. This is survey information and I need to count the missing inputs from the user which if the status is complete, inputs should be 6. If the status is In Progress, the user should have completed 4 inputs. Hope this helps and thank you very much for your efforts.

AddressStatusControlLevelExpected Completion Date (mm/dd/yyyy)Estimated Cost ($)Completed Date (mm/dd/yyyy)Actual Cost ($)Missing Inputs
1In ProgressEmployee TrainingLow5/1/20200  0
2Complete      6
3In Progress      4
4TemporaryGatesLow9/30/20190  0
5Temporary      4
6CompleteStandoffLow12/20/2019850012/21/201990000

I've tried creating complex nested IF statements and now trying to use Switch (True()) but can't seem to figure out how to accurately count missing values in 4-6 columns depending on the Status column category. For example,

 

Missing values =
if(
'Items'[Status]="Complete",
SWITCH( TRUE(),
'Items'[Level] = "Incomplete", 1,
ISBLANK('Items'[Control]), 1,
ISBLANK('Items'[Expected Completion Date (mm/dd/yyyy)]), 1,
ISBLANK('Items'[Estimated Cost ($)]), 1,
ISBLANK('Items'[Completed Date (mm/dd/yyyy)]), 1,
ISBLANK('Items'[Actual Cost ($)]), 1
) ,
'Items'[Status]="In Progress",
SWITCH( TRUE(),
'Items'[Level] = "Incomplete", 1,
ISBLANK('Items'[Control]), 1,
ISBLANK('Items'[Expected Completion Date (mm/dd/yyyy)]), 1,
ISBLANK('Items'[Estimated Cost ($)]), 1,
),0)
 
Of I also tried something like

IF(

            'Items'[Remediation Status]="On Hold",

            IF(ISBLANK('Items'[Control]),1,0) +

            IF('Items'[Level]="Incomplete",1,0) +

            IF(ISBLANK('Items'[Estimated Cost ($)]),1,0) +

            IF(ISBLANK('Items'[Expected Completion Date (mm/dd/yyyy)]),1,0),

       

            IF('Items'[Status]="Complete",

                IF(ISBLANK('Items'[Control]),1,0) +

                IF('Items'[Level]="Incomplete",1,0) +

                IF(ISBLANK('Items'[Estimated Cost ($)]),1,0) +

                IF(ISBLANK('Items'[Expected Completion Date (mm/dd/yyyy)]),1,0)+

                IF(ISBLANK('Items'[Completed Date (mm/dd/yyyy)]),1,0)+

                IF(ISBLANK('Items'[Actual Cost ($)]),1,0),

 

None of these scripts worked accurately and I need something like this

Annotation 2020-03-13 121455.jpg

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This is not a measure, it's a calculated column. Your formula can be written like this:

 

Missing Inputs = 0 +
    ( 'RRR items'[Status] = "Complete" ) * (
    
   	  	ISBLANK ( 'RRR items'[Control] )
        + ( 'RRR items'[Level] = "Incomplete" )
        + ISBLANK ( 'RRR items'[Estimated Cost ($)] )
        + ISBLANK ( 'RRR items'[Expected Completion Date (mm/dd/yyyy)] )
        + ISBLANK ( 'RRR items'[Completed Date (mm/dd/yyyy)] )
        + ISBLANK ( 'RRR items'[Actual Cost ($)] )
    ) 
    +  
    ( 'RRR items'[Status] = "In Progress" ) * (
        
    	ISBLANK ( 'RRR items'[Control] )
        + ('RRR items'[Level] = "Incomplete" )
        + ISBLANK ( 'RRR items'[Estimated Cost ($)] )
        + ISBLANK ( 'RRR items'[Expected Completion Date (mm/dd/yyyy)] )
    )

 

Best

D

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

By the way... I think you should do this in Power Query, not in DAX.

Best
D
Anonymous
Not applicable

Instead of writing:

IF(ISBLANK('Items'[Control]),1,0)

 you can write

1 * ISBLANK('Items'[Control])

If you want to add 2+ values that are logical, you can do:

condition1 + condition2 +...+ conditionN

This saves on typing IF's...

 

Best

D

 

Anonymous
Not applicable

Hi, thanks for the quick response. My understanding is to count the emply cells as follows

 

Missing Inputs =
IF(
'RRR items'[Status] = "Complete",
ISBLANK('RRR items'[Control]) + IF('RRR items'[Level]="Incomplete", 1) + ISBLANK('RRR items'[Estimated Cost ($)]) +
ISBLANK('RRR items'[Expected Completion Date (mm/dd/yyyy)]) + ISBLANK('RRR items'[Completed Date (mm/dd/yyyy)]) + ISBLANK('RRR items'[Actual Cost ($)]),
IF(
'RRR items'[Status] = "In Progress",
ISBLANK('RRR items'[Control]) + IF('RRR items'[Level]="Incomplete", 1) + ISBLANK('RRR items'[Estimated Cost ($)]) +
ISBLANK('RRR items'[Expected Completion Date (mm/dd/yyyy)]),
0))
 
The issue is that unless there is a unique identifier, the equation will total based on the address but each row should be counted as unique.
Anonymous
Not applicable

This is not a measure, it's a calculated column. Your formula can be written like this:

 

Missing Inputs = 0 +
    ( 'RRR items'[Status] = "Complete" ) * (
    
   	  	ISBLANK ( 'RRR items'[Control] )
        + ( 'RRR items'[Level] = "Incomplete" )
        + ISBLANK ( 'RRR items'[Estimated Cost ($)] )
        + ISBLANK ( 'RRR items'[Expected Completion Date (mm/dd/yyyy)] )
        + ISBLANK ( 'RRR items'[Completed Date (mm/dd/yyyy)] )
        + ISBLANK ( 'RRR items'[Actual Cost ($)] )
    ) 
    +  
    ( 'RRR items'[Status] = "In Progress" ) * (
        
    	ISBLANK ( 'RRR items'[Control] )
        + ('RRR items'[Level] = "Incomplete" )
        + ISBLANK ( 'RRR items'[Estimated Cost ($)] )
        + ISBLANK ( 'RRR items'[Expected Completion Date (mm/dd/yyyy)] )
    )

 

Best

D

Greg_Deckler
Community Champion
Community Champion

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.