check latest status

[jasperdavid]

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.

[v-kelly-msft · Accepted solution]

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!

[amitchandak]

try to have a measure like this

calculate(countrows(table),allexcept(table,table[part]),table[status]="O"), and use this to filter

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin