Forum Discussion

Mous007's avatar
Mous007
Icon for Helper IV rankHelper IV
6 years ago
Solved

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

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

Calculate function with multiple criteria
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      Mous007
      Icon for Helper IV rankHelper IV
      6 years ago

      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