Is there a way to filter out columns based on conditions?

[rhettrosario22]

Is there a way to filter out columns based on conditions. I have a scenario where i need to only show the group who is awaiting a response and have a group filtered out if one person in that group has approved. In this scenario the group procurement is listed twice but since one person in that group approved the title, i would want them completely filtered out

[rsbin · Accepted solution]

@rhettrosario22 ,

You can create a Calculated Column as follows:

ApprovedFlag = 
VAR _Concat = CALCULATE( CONCATENATEX( YourTable, [Column2], "," ), ALLEXCEPT( YourTable, YourTable[Title] ))
VAR _Result = IF( CONTAINSSTRING( _Concat, "Approved" ), "Approved", "Not Approved")
RETURN
    _Result

I hope this is the Result you are looking for.

Column1	Column2	Title	ApprovedFlag
Quest Diagnostics	Approved	Procurement	Approved
Quest Diagnostics	Approved	Technology	Approved
Quest Diagnostics	Awaiting Response	Corporate Counsel	Not Approved
Quest Diagnostics	Awaiting Response	Procurement	Approved
Quest Diagnostics	Awaiting Response	Technology	Approved

In the future, please paste sample data as text not as a picture.  Makes it easier for folks to lend assistance.

Regards,

[rsbin · Accepted solution]

@rhettrosario22 ,

You can create a Calculated Column as follows:

ApprovedFlag = 
VAR _Concat = CALCULATE( CONCATENATEX( YourTable, [Column2], "," ), ALLEXCEPT( YourTable, YourTable[Title] ))
VAR _Result = IF( CONTAINSSTRING( _Concat, "Approved" ), "Approved", "Not Approved")
RETURN
    _Result

I hope this is the Result you are looking for.

Column1	Column2	Title	ApprovedFlag
Quest Diagnostics	Approved	Procurement	Approved
Quest Diagnostics	Approved	Technology	Approved
Quest Diagnostics	Awaiting Response	Corporate Counsel	Not Approved
Quest Diagnostics	Awaiting Response	Procurement	Approved
Quest Diagnostics	Awaiting Response	Technology	Approved

In the future, please paste sample data as text not as a picture.  Makes it easier for folks to lend assistance.

Regards,

[rhettrosario22]

Hi,

Thank you for your help. I am having an issue where it is only populating as "approved". Is it because there is another field being used (Contract Status)

Here is what i put in as the formula

ApprovedFlag =

VAR _Concat = CALCULATE( CONCATENATEX( 'Contract Approvals', [Approval Status], "," ), ALLEXCEPT( 'Contract Approvals', 'Contract Approvals'[Approval Group] ))

VAR _Result = IF( CONTAINSSTRING( _Concat, "Approved" ), "Approved", "Not Approved")

RETURN

    _Result

Here is the full Table

Contract Title	Approval Status	Contract Title	Contract Status	Approved Flag
Quest Diagnostics	Awaiting Response	Procurement	Out For Approval	Approved
Quest Diagnostics	Approved	Technology	Out For Approval	Approved
Quest Diagnostics	Awaiting Response	Corporate Counsel	Out For Approval	Approved
Quest Diagnostics	Approved	Procurement	Out For Approval	Approved
Quest Diagnostics	Awaiting Response	Technology	Out For Approval	Approved

[rsbin · Accepted solution]

@rhettrosario22 ,

Your Measure should work:

ApprovedFlag = 
VAR _Concat = CALCULATE( CONCATENATEX( 'Contract Approvals', [Approval Status], "," ), ALLEXCEPT( 'Contract Approvals', 'Contract Approvals'[Approval Group] ))
VAR _Result = IF( CONTAINSSTRING( _Concat, "Approved" ), "Approved", "Not Approved")
RETURN
    _Result

Please check your Column Names.  You have provided a table with Contract Title twice and no column name [Approval Group].  I assumed your Procurement column should be called [Approval Group]

Contract Title	Approval Status	Approval Group	Contract Status	ApprovedFlag
Quest Diagnostics	Awaiting Response	Procurement	Out For Approval	Approved
Quest Diagnostics	Approved	Technology	Out For Approval	Approved
Quest Diagnostics	Awaiting Response	Corporate Counsel	Out For Approval	Not Approved
Quest Diagnostics	Approved	Procurement	Out For Approval	Approved
Quest Diagnostics	Awaiting Response	Technology	Out For Approval	Approved

Please see if this works for you.

Regards,

[rhettrosario22]

Thank you ! i got it to work