Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

filling empty cells with 0

Hello,

 

I would like to fill empty cell with zero, how can I do that? As you see on the picture, I have some empty cells...

This is my measure 

Total Pending =
CALCULATE(
    COUNTROWS('CecCaseDetail H'),
    'CecCaseDetail H'[Case] IN {"New", "Enhanced", "Stand" }
)

 

Need Help
  • AnkitaaMishra's avatar
    AnkitaaMishra
    1 year ago

    Hi Anonymous , 
    Try below code : 
    Total Pending =
    COALESCE(
    CALCULATE(
    COUNTROWS('CecCaseDetail H'),
    'CecCaseDetail H'[Case] IN {"New", "Enhanced", "Stand"}
    ),
    0
    )

    Thanks, 
    Ankita

6 Replies

  • AnkitaaMishra's avatar
    AnkitaaMishra
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous , 
    Try below code : 
    Total Pending =
    COALESCE(
    CALCULATE(
    COUNTROWS('CecCaseDetail H'),
    'CecCaseDetail H'[Case] IN {"New", "Enhanced", "Stand"}
    ),
    0
    )

    Thanks, 
    Ankita

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    It works Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    And for this?

     

    NewCasesPrecCount =
    VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
    RETURN
        CALCULATE(
            COUNT('CecCaseDetail'[CaseID]),
            'CecCaseEvent'[EventID] = 1,
            YEAR('CecCaseEvent'[EventDate]) = SelectedYear,
            'CecCaseEvent'[CecCaseId] = 'CecCaseEvent'[CecCaseId],
            FILTER(
                'CecLeadingCase',
                'CecLeadingCase'[CecCaseId] = 'CecLeadingCase'[CecCaseId]
            )
        )
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      just add COALESCE? Right?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        yes it works 🙂

    • AnkitaaMishra's avatar
      AnkitaaMishra
      Icon for Super User rankSuper User
      1 year ago

      Similarly Anonymous , you can use again COALESCE function : 

      NewCasesPrecCount =
      VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
      RETURN
      COALESCE(
          CALCULATE(
              COUNT('CecCaseDetail'[CaseID]),
              'CecCaseEvent'[EventID] = 1,
              YEAR('CecCaseEvent'[EventDate]) = SelectedYear,
              'CecCaseEvent'[CecCaseId] = 'CecCaseEvent'[CecCaseId],
              FILTER(
                  'CecLeadingCase',
                  'CecLeadingCase'[CecCaseId] = 'CecLeadingCase'[CecCaseId]
              )
          ),
      0
      )