Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Count 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/...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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])
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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?  

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 

       

      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:

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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?