How to count blanks as Failed?

[rile14]

Hi All,

I have two columns, Status and ID. In Status column I have 3 values: blank, Failed and Succeeded. What I need is to display a count of ID's per Status, where blanks should be counted as failed.

So, here I would filter a visual to display just Failed and Succeeded, but to see 15 under Failed (Failed + blanks) and 61 under Succeeded.

Can someone help me on how to solve this?

Thanks in advance.

[Anonymous · Accepted solution]

@rile14 Can you consider replacing the blannk ones, with failed status in data model?

[VahidDM]

Hi @rile14 

Try this measure:

Count Status =
VAR _CSucceeded =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status ] = "Succeeded" )
RETURN
    IF (
        MAX ( 'Table'[Status ] ) = "",
        BLANK (),
        IF (
            MAX ( 'Table'[Status ] ) = "Succeeded",
            _CSucceeded,
            COUNTROWS ( ALL ( 'Table' ) ) - _CSucceeded
        )
    )

You don't need to set any filter.

My table:

Output:

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

Appreciate your Kudos  !!

[rile14]

Thank you Vahid. Your solution works the same as the one provided by @Fowmy  But with both solutions there is another issue now. The total is not correct:

Do you know how this could be fixed?

Thanks.

[Fowmy · Accepted solution]

@rile14 

The best approach is to replace blank with Failed in Power Query in your data source. 

If you need  a dax solution the create a measure as follows but you need to filter out the balnk in filter pane to get a better pciture

M2 = 
VAR __Status = MAX(Table5[Batch Status]) 
Return
SWITCH(
    TRUE(),
    __Status = "Failed" , 
        CALCULATE(
            COUNTA(Table5[Batch Status]),
            Table5[Batch Status] <> "Succeeded"
        ),
    COUNTA(Table5[Batch Status])
)

My data

Result (I unchecked Blank)

[rile14]

Thank you for helping me with this.

Unfortunately I can't replace blanks in data source.

DAX would be perfect solution for me. Result from your example is what I need. But when I tried to apply that DAX on my case I got different result:

First column is just count of IDs and in second column is measure that you provided. Blank is not visible, but still it's not added to the Failed count. I would expect to see 15 under Failed.

Am I missing something?

M2 =

VAR __Status = MAX('Batch'[BatchStatus])

Return

SWITCH(

TRUE(),

__Status = "Failed",

CALCULATE(

COUNTA('Batch'[BatchStatus]),

'Batch'[BatchStatus] <> "Succeeded"

),

COUNTA('Batch'[BatchStatus])

)

[rile14]

UPDATE: I fixed this. I just needed to count batch id instead of batch status. Thank you very much. 🙂

[Anonymous · Accepted solution]

@rile14 Can you consider replacing the blannk ones, with failed status in data model?