topic Re: Count Blanks Based on Two Columns Within Month in DAX Commands and Tips

Count Blanks Based on Two Columns Within Month

BrianNeedsHelp — Tue, 22 Oct 2024 17:57:52 GMT

I'm trying to Count the number of blanks across date range filtered to a month, where it counts only if both columns are blank for a particular day. Like this:

	10/1/2024	10/1/2024	10/2/2024	10/2/2024	10/3/2024	10/3/2024	Count Blanks
	Sales	Upsales	Sales	UpSales	Sales	Upsales
Customer A	3		1	1			1
Customer B	4	5	1	7	2	3	0
Customber C		1					2

I couldn't use CountBlank, because I kept getting "parameter is the wrong type" even though I used Value([Sales]), so I tried like this CountBlanks = COUNTROWS(FILTER('Sales Table', ISBLANK([Sales] && ISBLANK([Upsales])))), but it didn't populate anything. If I just use one side of the && statement, it populates wild values. Any way to make this work?

Re: Count Blanks Based on Two Columns Within Month

lbendlin — Tue, 22 Oct 2024 21:35:28 GMT

To report on things that are not there you need to use disconnected tables and/or crossjoins

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Count Blanks Based on Two Columns Within Month

BrianNeedsHelp — Wed, 23 Oct 2024 00:26:50 GMT

I keep getting "Your post has been changed because invalid HTML was found in the message body." I clicked on the table and selected only one square like the instructions and pasted from a spreadsheet. I'm needing to know how I can count blanks if two columns are blank. If there are dates going across the top of the table 10/1 10/2 10/3 etc. Each date has two columns, Sales and Upsales. If both columns are blank that should count as 1. The Count Blanks column would have the total number of blanks across the whole date range.

Re: Count Blanks Based on Two Columns Within Month

Anonymous — Wed, 23 Oct 2024 02:20:42 GMT

Hi BrianNeedsHelp

Here's a sample for your reference:

Sales Table:

Then add a measure:

CountBlank = VAR _currentCustomer = SELECTEDVALUE ( 'Sales Table'[Customer] ) RETURN CALCULATE ( COUNT ( 'Sales Table'[Date] ), FILTER ( ALLSELECTED ( 'Sales Table' ), 'Sales Table'[Sales] = BLANK () && 'Sales Table'[Upsales] = BLANK () && 'Sales Table'[Customer] = _currentCustomer ) )

The result is as follow:

The above examples are for your reference, and you need to adjust the visualization yourself.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Blanks Based on Two Columns Within Month

BrianNeedsHelp — Wed, 23 Oct 2024 02:30:18 GMT

Thank you, but it only needs to count blank if both fields are blank within the date. And is there a way to hide the CountBlanks field within the dates and just show a sum total at the end of the row?

Re: Count Blanks Based on Two Columns Within Month

Anonymous — Wed, 23 Oct 2024 02:41:24 GMT

Hi BrianNeedsHelp

The matrix does not currently support the ability to hide fields, so you can only make it appear empty

Please try this:

CountBlank = VAR _currentCustomer = SELECTEDVALUE ( 'Sales Table'[Customer] ) RETURN IF ( NOT HASONEVALUE ( 'Sales Table'[Date] ), CALCULATE ( COUNT ( 'Sales Table'[Date] ), FILTER ( ALLSELECTED ( 'Sales Table' ), 'Sales Table'[Sales] = BLANK () && 'Sales Table'[Upsales] = BLANK () && 'Sales Table'[Customer] = _currentCustomer ) ) )

The result is as follow:

For the sum column of Sumofeles and Sumofopsales, you can also create Messur to do the same, for example:

_Sales = IF(HASONEVALUE('Sales Table'[Date]),SUM('Sales Table'[Sales]))

_Upsales = IF(HASONEVALUE('Sales Table'[Date]),SUM('Sales Table'[Upsales]))

The result is as follow:

Re: Count Blanks Based on Two Columns Within Month

BrianNeedsHelp — Wed, 23 Oct 2024 04:45:20 GMT

So the Locations are in a different table, so I'm having trouble with the last part of the code. I have this:

Blanks = VAR _currentCustomer = SELECTEDVALUE ( 'Current Hierarchy'[Location] ) RETURN IF ( NOT HASONEVALUE ('Calendar'[Calendar Date] ), CALCULATE ( COUNT ( 'Calendar'[Calendar Date]), FILTER ( ALLSELECTED ( 'Sales'), 'Sales'[Sales]= BLANK () && 'Sales'[Upsales]= BLANK () && 'Current Hierarchy'[Location]=_currentCustomer ) ) )

The Customer is [Location] is in a different table, so it's not allowing me to add in 'Current Hierarchy' [Location]that in under the All Selected ('Sales')table. Can that be reformatted to add that part?

Re: Count Blanks Based on Two Columns Within Month

BrianNeedsHelp — Wed, 23 Oct 2024 18:29:47 GMT

Anonymous I got the code inputted with no errors, but in the Total I get the number 31 in all of the rows. So it's like it's saying there are blanks every day for the whole month.

Re: Count Blanks Based on Two Columns Within Month

Anonymous — Thu, 24 Oct 2024 01:41:11 GMT

Hi BrianNeedsHelp

Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Blanks Based on Two Columns Within Month

BrianNeedsHelp — Thu, 24 Oct 2024 02:30:09 GMT

Thx for checking, but I ended up doing something much simpler:

CountBlanks = IF(ISBLANK([Gross Adds])&&ISBLANK([Device Upgrades]), 1, 0)

Then I put in a measure to sum the CountBlanks:

#DaysZero = SUMX(VALUES('Calendar'[CalendarDate]),[CountBlanks])