How to filter a value based on many instances of another value

[anmattos]

Greetings,

I have a table that follows the logic of the table below. I need to create a filter that only shows the Requirements that have all status in the second column as "Approved".

In the example given below the filter would only show Requirement B as all status in the second column equals "Approved". Requiments A and C should be filtered out since they have other status as "Draft" and "In Approval", even though they may have an "Approved" thrown in.

Any idea on how to do that? A calculated column perhaps?

Best regards,

Requirements		Status
Requirement A		Draft
Requirement A		In Approval
Requirement A		Approved
Requirement A		Draft
Requirement A		Draft
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement C		In Approval
Requirement C		Draft
Requirement C		In Approval
Requirement C		Approved
Requirement C		Draft

Output in a table visual would be :

Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved
Requirement B		Approved

SDS

[anmattos]

Hello,

Please, could anyone help me with this filter?

Regards,

[v-kelly-msft · Accepted solution]

Hi  @anmattos ,

Create a column as below:

Column =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "Approved"
                && 'Table'[Requirements] = EARLIER ( 'Table'[Requirements] )
        )
    ) + 0
RETURN
    IF ( _count = 0, 'Table'[Requirements], BLANK () )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[anmattos]

Thank you very much @v-kelly-msft . It seems to be the solution. I´m having a little bit of trouble testing it right now, but will mark as a solution afterwards.

Quick question: If I wanted to check for more than 1 status as valid, like any combination of "Approval" and "In Approval", how I would do that in the formula? Like this?

'Table'[Status] <> "Approved" or "In Approval"

Regards,

[v-kelly-msft]

Hi  @anmattos ,

Yes,you're right.

Or you can use below formula:

Column = 
var _count=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),NOT('Table'[Status] in {"Approved","In Approval"})&&'Table'[Requirements]=EARLIER('Table'[Requirements])))+0
Return
IF(_count=0,'Table'[Requirements],BLANK())

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[anmattos]

Thank you very much!

[rodrigosan]

I suggest creating a conditional column.
Would be like this:

[anmattos]

Thank you for your reply, but this would not work since I would not be able to filter out ONLY the requirements that have Approved status on ALL instances. Thanks you.

[rodrigosan]

When you refer to all instances.
Do you mean tables?
Could you explain it better?

[anmattos]

I mean all the times that a value of requirement appears the status column must be approved for all these lines. For example, Requirement B is the only one that qualifies for the filter since all the lines in which Requirement B appears the Status is Approved. For the other requirements, even though they may have an Approved status for one or two rows, they don't have it for ALL the rows and thus does not qualify to be in the output.

[amitchandak]

@anmattos , plot this measure with Requirements, this should give you only Requirements having all status as Approved

countx(filter(summarize(Table, Table[Requirements], "_1",calculate(distinctCOUNT(Table[Status]), Table[Status] = "Approved"),"_2", distinctCOUNT(Table[Status])), [_1] =[_2]),[Requirements])

[anmattos]

Hello @amitchandak ,

Thank you for your answer. I´m a novice so did not understand how to implement it. Should I create a calculated column, a measure or include this in a visual (in my case would be a table)?

Regards,