Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Latest status for unique value only

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!

 

Need Help

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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?