Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Inspection Dataset Measures

I have an inspection dataset similar to the below.   ID Date Result 1 01/01/2022 Pass 1 02/01/2022 Fail 2 01/01/2022 Pass 2 02/01/2022 Pass 2 03/01/2022 Fail   I ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    I modify these measures, please try them:

     

    Datediff = 
VAR startdate =
    CALCULATE ( MIN ( 'Table'[Date] ), 'Table'[Result] = "Pass" )
VAR enddate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        'Table'[Result] = "Fail",
        'Table'[Date] >= startdate
    )
RETURN
    DATEDIFF ( startdate, enddate, DAY ) + 0
    Total recieve Pass then Fail = SUMX ( VALUES ( 'Table'[ID] ), [recieve Pass then Fail] )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

Hi Anonymous ,

 

Please try the following formula:

 

Datediff = 
VAR startdate =
    CALCULATE ( MIN ( 'Table'[Date] ), 'Table'[Result] = "Pass" )
VAR enddate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        'Table'[Result] = "Fail",
        'Table'[Date] >= startdate
    )
RETURN
    DATEDIFF ( startdate, enddate, DAY )
Average time = AVERAGEX ( VALUES ( 'Table'[ID] ), [Datediff] )
CountResultPerID = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Result] ),
    ALLEXCEPT ( 'Table', 'Table'[ID] )
)
only recieve Pass = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER ( 'Table', 'Table'[Result] = "Pass" && [CountResultPerID] = 1 )
)
recieve Pass then Fail =
VAR Firstday =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            'Table',
            'Table'[Date] = Firstday
                && 'Table'[Result] = "Pass"
                && [CountResultPerID] = 2
        )
    )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
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
    4 years ago

    Hi,

     

    Thanks for your response. I have added all measures into my PBIX file and are recieving some errors.

     

    Looking at ID 1796 in particalar I have the below data.

    IDDateResult
    179615/12/2021Pass
    179608/12/2021Pass
    179601/12/2021Pass
    179624/11/2021Pass
    179617/11/2021Pass
    179610/11/2021Pass
    179603/11/2021Pass
    179627/10/2021Pass
    179620/10/2021Pass
    179613/10/2021Pass
    179606/10/2021Pass
    179629/09/2021Pass
    179622/09/2021Pass
    179615/09/2021Pass
    179608/09/2021Pass
    179601/09/2021Pass
    179625/08/2021Fail

     

    I am seeing the below results which is not correct as this specific ID has a fail.

     

    Any ideas?

    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      I modify these measures, please try them:

       

      Datediff = 
VAR startdate =
    CALCULATE ( MIN ( 'Table'[Date] ), 'Table'[Result] = "Pass" )
VAR enddate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        'Table'[Result] = "Fail",
        'Table'[Date] >= startdate
    )
RETURN
    DATEDIFF ( startdate, enddate, DAY ) + 0
      Total recieve Pass then Fail = SUMX ( VALUES ( 'Table'[ID] ), [recieve Pass then Fail] )

       

      If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
      Best Regards,
      Winniz
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.