cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Count Blanks with 2 conditions/filters

I wonder if the below is possible...

 

I have a data set like this:

 

OwnerTest ResultTest request dateTest result date
SallyYes25/06/202027/06/2020
SallyYes15/06/202020/06/2020
DaveNo10/06/2020 
DaveNot Applicable  
SallyNot Applicable  

 

I am creating a report that monitors the number of blanks fields to ensure data is being entered. 

 

If the test is not applicable, I don't want to count the blanks as they are not needed. However if the Test Result is Yes or No, there should be a date for the Request and the Result. 

 

What would be the correct way to show this in a matrix as a count of the blanks? E.g. I'd like my matrix to look like...

 

NameBlank Test Request DateBlank Test Return Date
Sally00
Dave01
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Count Blanks with 2 conditions/filters

@Aimeeclaird 

Please try this

blank test request date = 
CALCULATE(COUNTBLANK('Table'[Test request date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0

blank test result date = 
CALCULATE(COUNTBLANK('Table'[Test result date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

4 REPLIES 4
Highlighted
Memorable Member
Memorable Member

Re: Count Blanks with 2 conditions/filters

@Aimeeclaird 

Please try this

blank test request date = 
CALCULATE(COUNTBLANK('Table'[Test request date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0

blank test result date = 
CALCULATE(COUNTBLANK('Table'[Test result date]),FILTER('Table','Table'[test result]="Yes"||'Table'[test result]="No"))+0

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Helper I
Helper I

Re: Count Blanks with 2 conditions/filters

@ryan_mayu 

That's brilliant, thanks for your reply. 

 

For my own understanding can you tell me if '||' is what is needed to apply 2 filters? I had previously tried to add to filters (similar formula to your solution) but got a dax error in the measure. 

Highlighted
Helper V
Helper V

Re: Count Blanks with 2 conditions/filters

Hi,

 

Can you try the below measure for counting test result blanks? The same needs to be created for other columns then use them.

 

BlankTestResultCheck =
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Test Result] = "Yes"
|| 'Table'[Test Result] = "NO",
'Table'[Test result date] = BLANK ()
)

Blankrowscount.PNG

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

Highlighted
Memorable Member
Memorable Member

Re: Count Blanks with 2 conditions/filters

@Aimeeclaird 

 

|| means OR, that will only count staus is Yes or No and will not count the rows with Not applicable status.

Please let me know the error message.

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors