count blanks in row across multiple columns dependent on category

[Anonymous]

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.

Address	Status	Control	Level	Expected Completion Date (mm/dd/yyyy)	Estimated Cost ($)	Completed Date (mm/dd/yyyy)	Actual Cost ($)	Missing Inputs
1	In Progress	Employee Training	Low	5/1/2020	0			0
2	Complete							6
3	In Progress							4
4	Temporary	Gates	Low	9/30/2019	0			0
5	Temporary							4
6	Complete	Standoff	Low	12/20/2019	8500	12/21/2019	9000	0

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

[Anonymous]

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

Best
D

[Anonymous]

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]

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 · Accepted solution]

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]

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