Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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,
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
Solved! Go to 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
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
Hi, thanks for the quick response. My understanding is to count the emply cells as follows
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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |