Distinct count for completed requests & completed requests <> failure or error status

[Mous007]

Hi guys,

I am culaculating the following measure on my report for completed requests:

Number of requests with Complete status = CALCULATE(DISTINCTCOUNT('Full History Table'[TRACKING_NUMBER]) ,FILTER('Full History Table','Full History Table'[STATUS]= "Complete"))

So basically my measure above counts all the requests that were completed without checking if they had any specific errors or failures.

My users are requesting another calculation where they need to see the number of completed requests but only for requests without Failures or errors ( both statuses are from the STATUS column also used above)

The stutus column include the following fields:

• Complete
• Failure
• System error
• Validation level1
• Validation level 2
• Cancelled

How can i please create the new measure or column ? or any other approach i can use ?

Please let me know if you need additional infos.

Best,

Mous

[sturlaws · Accepted solution]

Hi @Mous007 

it is always a good idea to provide some sample data, or sample file, when you post a question. You will get a more accurate answer, and usually users of this forum is quicker at replying posts with sample data.

You can try something like this:

Number of requests with Complete status without failures =
VAR _allComplete =
    CALCULATETABLE (
        VALUES ( 'Full History Table'[TRACKING_NUMBER] ),
        FILTER ( 'Full History Table', 'Full History Table'[STATUS] = "Complete" )
    )
VAR _withFailures =
    CALCULATETABLE (
        VALUES ( 'Full History Table'[TRACKING_NUMBER] ),
        FILTER ( 'Full History Table', NOT ( ISBLANK ( Failure ) ) )
    )
VAR _withoutFailures =
    EXCEPT ( _allComplete, _withFailures )
RETURN
    COUNTROWS ( _withoutFailures )

You will probably have to change this statement, since I don't know how a failure is described:

CALCULATETABLE (
        VALUES ( 'Full History Table'[TRACKING_NUMBER] ),
        FILTER ( 'Full History Table', NOT ( ISBLANK ( Failure ) ) )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Mous007]

Hi @sturlaws, thank you for your reply.

I managed to get the expected results by modfiying the forumla as follow:

VAR _withFailures =

CALCULATETABLE (

VALUES ( 'Full History Table'[TRACKING_NUMBER] ),

FILTER ( 'Full History Table', OR( 'Full History Table'[STATUS] = "Validation Failed", 'Full History Table'[STATUS] = "IFACE error")

))

Thanks again for the huge help @sturlaws 

Best, 
Mous