Forum Discussion

chrs-'s avatar
chrs-
Frequent Visitor
5 years ago
Solved

Dataset with duplicate serial numbers - Correct filtering

Hello,

 

I'm quite new handling Power BI. I have a table like this:

Serial NumberTime StampResultResult BooleanTest Counter
10000019.02.2021 11:15NOK11
10000019.02.2021 11:30OK02
10000119.02.2021 11:45OK01

 

I want to create a measure where all Serial Numbers are counted where the first test (Test Counter = 1) is NOK (Result Boolean = 1) and the second test (Test Counter = 2) is OK (Result Boolean = 0). I don't know how to do this. Can anyone help? Thanks.

 

Edit: Result in this case would be a total of 1 (Serial Number 100000)

 

Kind regards

Christian

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi chrs- ,

     

    First create an index column;

    Then create 2 measures as below:

    Measure 3 = 
VAR _next=CALCULATE(MAX('Table'[Test Counter]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])+1))+0
var _previous=CALCULATE(MAX('Table'[Test Counter]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])-1))+0
var _result1=CALCULATE(MAX('Table'[Result]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])+1))
var _result2=CALCULATE(MAX('Table'[Result]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])-1))
var _resultboolean1=CALCULATE(MAX('Table'[Result Boolean]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])+1))
var _resultboolean2=CALCULATE(MAX('Table'[Result Boolean]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])-1))
Return
IF(MAX('Table'[Test Counter])=1&&_next=2 ||MAX('Table'[Test Counter])=2&&_previous=1,
IF(MAX('Table'[Result])="NOK"&&_result1="OK"||MAX('Table'[Result])="OK"&&_result2="NOK",
IF(MAX('Table'[Result Boolean])=1&&_resultboolean1=0||MAX('Table'[Result Boolean])=0&&_resultboolean2=1,1,0)))
    Measure 4 = SUMX(VALUES('Table'[Serial Number]),[Measure 3]+0)

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

5 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi chrs- ,

     

    First create an index column;

    Then create 2 measures as below:

    Measure 3 = 
VAR _next=CALCULATE(MAX('Table'[Test Counter]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])+1))+0
var _previous=CALCULATE(MAX('Table'[Test Counter]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])-1))+0
var _result1=CALCULATE(MAX('Table'[Result]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])+1))
var _result2=CALCULATE(MAX('Table'[Result]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])-1))
var _resultboolean1=CALCULATE(MAX('Table'[Result Boolean]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])+1))
var _resultboolean2=CALCULATE(MAX('Table'[Result Boolean]),FILTER(ALL('Table'),'Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[Index]=MAX('Table'[Index])-1))
Return
IF(MAX('Table'[Test Counter])=1&&_next=2 ||MAX('Table'[Test Counter])=2&&_previous=1,
IF(MAX('Table'[Result])="NOK"&&_result1="OK"||MAX('Table'[Result])="OK"&&_result2="NOK",
IF(MAX('Table'[Result Boolean])=1&&_resultboolean1=0||MAX('Table'[Result Boolean])=0&&_resultboolean2=1,1,0)))
    Measure 4 = SUMX(VALUES('Table'[Serial Number]),[Measure 3]+0)

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

  • chrs-'s avatar
    chrs-
    Frequent Visitor
    5 years ago

    DistinctCount provides the result 2 because there are two unique Serial Numbers. So this is not what I am looking for. 

     

    I have 3 conditions:

    1. Matching Serial Number (in this case 100000)

    2. 1st test (Test Counter = 1) where Result = NOK (Result Boolean = 1)

    3. 2nd test (Test Counter = 2) where Result = OK (Reslut Boolean = 0)

     

    My problem is that I don't know how to use calculate in this case and set the filters correctly.

     

    Christian

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hey chrs- 

    with my dataset I and measure I get the following solution:

     

    Measure = CALCULATE(
    DISTINCTCOUNT('Table'[Serial Number]),
    'Table'[Result] = "NOK",
     'Table'[Result Boolean] = 1
    )

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • chrs-'s avatar
    chrs-
    Frequent Visitor
    5 years ago

    Thanks for your help Tahreem24 and FrankAT ğŸ™‚
    In this case the result in incorrect (see the conditions) for Serial Number 100003

    Going trough the conditions:

    1. Matching Serial Number (in this case 100000) ----> Correct

    2. 1st test (Test Counter = 1) where Result = NOK (Result Boolean = 1) -----> Correct

    3. 2nd test (Test Counter = 2) where Result = OK (Reslut Boolean = 0) ---> there is no 2nd test for this serial

     

    Result here with this dataset should be 2 as well. Only 100000 and 100002 suit the conditions.

     

    Thank you

    Christian