Dataset with duplicate serial numbers - Correct filtering

[chrs-]

Hello,

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

Serial Number	Time Stamp	Result	Result Boolean	Test Counter
100000	19.02.2021 11:15	NOK	1	1
100000	19.02.2021 11:30	OK	0	2
100001	19.02.2021 11:45	OK	0	1

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

[v-kelly-msft · Accepted solution]

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-]

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

[FrankAT]

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-]

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

[Tahreem24]

@chrs- try the below measure:

Measure = DistinctCount(Table[Serial number])