Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
Hello,
Please, could anyone help me with this filter?
Regards,
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!
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,
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!
Thank you very much!
I suggest creating a conditional column.
Would be like this:
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.
When you refer to all instances.
Do you mean tables?
Could you explain it better?
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.
@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])
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |