Forum Discussion
Resolver ICount Blanks Based on Two Columns Within Month
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?
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])
9 Replies
- lbendlin
Super User
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
BrianNeedsHelpI 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.
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.- BrianNeedsHelp
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?
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:
