cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pakkasruska Frequent Visitor
Frequent Visitor

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!

 

3 REPLIES 3
Super User
Super User

Re: Latest status for unique value only

@Pakkasruska

 

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

 

Pakkasruska Frequent Visitor
Frequent Visitor

Re: Latest status for unique value only

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?

Highlighted
Community Support Team
Community Support Team

Re: Latest status for unique value only

@Pakkasruska,

 

You may take a look at the following post.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.