Forum Discussion

jasperdavid's avatar
jasperdavid
Frequent Visitor
6 years ago
Solved

check latest status

Hey guys,

 

I have a table with the results of certain inspections of different parts.

If the inspection was NG ::> status = X

If the inspection was Ok or concession, status = O or C

 

I would like to have a measure that calculates how many parts are still open (so X was given, but not yet O)

 

Part 1  X  01/01/2020

Part 2 X  02/01/2020

Part 1  X  03/ 01/2020

Part 3  X  03/01/2020

Part 2 O 05/01/2020

Part 4 O 06/01/2020

 

So for the example above: the measure should show 2 parts (as part 1 & part 3 have not received O yet), and part 2 has received O so should be excluded from the data.

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

    Hi jasperdavid ，

     

    You need a measure as below:

     

    Measure =
VAR a =
    CALCULATE (
        COUNT ( 'Table'[status] ),
        ALLEXCEPT ( 'Table', 'Table'[Part] ),
        'Table'[status] <> "O"
    )
VAR b =
    CALCULATE ( COUNT ( 'Table'[status] ), ALLEXCEPT ( 'Table', 'Table'[Part] ) )
RETURN
    IF ( a = b, a, BLANK () )

     

    Finally ,you will see:

     

     

    For the related .pbix file,pls click here.

     


     
    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

     

2 Replies