Latest status for unique value only

[Anonymous]

Hi PowerBI fanatics,

I am facing the following issue:

I have the following columns:

Domain - Item Identifier - Status - Date

Domain A -101 - Bad - 01-01-2019

Domain A - 101 - Bad - 02-01-2019

Domain A -101 - Good - 03-01-2019

Domain B - 102 - Good - 01-01-2019

Domain B - 103 - Good - 01-01-2019

Domain A - 104 - Bad - 01-01-2019

Domain A - 104 - Bad - 02-01-2019

What I would need is a overview of

- the unique cases that have status 'bad' only (if a case is bad twice, only count it once)

- the 'bad' items that were eventually good (after 2 days) should not be counted

In other words, I need an overview per domain of all the unique cases that were truly 'bad' and not if they were 'good' during a later day.

Many thanks!

[v-chuncz-msft]

@Anonymous,

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/DAX-Grouping-tagging/m-p/189391#M83354

[Zubair_Muhammad]

@Anonymous

Try these MEASURES and see the attached file

Names of Bad Cases =
CONCATENATEX (
    FILTER (
        VALUES ( Table1[ Item Identifier ] ),
        CALCULATE ( COUNTROWS ( Table1 ) )
            = CALCULATE ( COUNTROWS ( Table1 ), Table1[ Status ] = "Bad" )
    ),
    [ Item Identifier ],
    ", "
)

Count of Bad Cases =
COUNTROWS (
    FILTER (
        VALUES ( Table1[ Item Identifier ] ),
        CALCULATE ( COUNTROWS ( Table1 ) )
            = CALCULATE ( COUNTROWS ( Table1 ), Table1[ Status ] = "Bad" )
    )
) + 0

[Anonymous]

Thank you for this, sir! I will directly try to implement this logic. @Zubair_Muhammad

Could you maybe also help with the following:

I need to count the 'Good' cases per domain and then multiply that with a certain amount

So the end result would be:

Domain - Good Amount # - Total

Domain A - 500 - 2000 euro

Domain B - 100 - 250 euro

Domain C - 2000 - 2500 euro

Do you have any ideas for this as well?