Check if all columns have specific value, otherwise displays those that don't

[Sweet-T]

Good morning everyone, 

I have multiple columns that contribute to deciding if an item is ready for payment. To be eligible for payment, all columns must be "Ready for Payment". I'd like to find a way (I'm thinking function or variables in DAX) to validate if each column for a given row meets my criteria, otherwise I'd like it to display the string(s) of the column(s) that do not meet my criteria.

In the following example, "Payment Eligibility" is my primary desired result, but if it is very difficult to concatenate and spit out the strings that don't meet the criteria, "Payment Eligibility 2.0" would be my next ask. 

Item	Column A	Column B	Column C	Payment Eligibility	Payment Eligibility 2.0
1	Ready for Payment	Ready for Payment	Ready for Payment	Ready for Payment	Ready for Payment
2	Ready for Payment	B Incomplete	Ready for Payment	B Incomplete	Incomplete
3	A Incomplete	B Incomplete	Ready for Payment	A Incomplete, B Incomplete	Incomplete

Hopefully someone out there has encountered something similar. Thanks in advance!

Happy Tuesday,

T

[v-lili6-msft · Accepted solution]

hi, @Sweet-T

You could try to use SEARCH Function to add a new column

Result = IF(SEARCH("Ready for Payment",'Table'[Column A],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column B],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column C],1,0)>0,"Ready for Payment",
IF(SEARCH("Ready for Payment",'Table'[Column A],1,0)=0,'Table'[Column A])&IF(SEARCH("Ready for Payment",'Table'[Column B],1,0)=0,'Table'[Column B])&IF(SEARCH("Ready for Payment",'Table'[Column C],1,0)=0,'Table'[Column C]))

Result 2.0 = IF(SEARCH("Ready for Payment",'Table'[Column A],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column B],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column C],1,0)>0,"Ready for Payment","	Incomplete")

By the way, Whether you do not put a comma between two fields, since these are three separate columns, that will be difficult to get it.

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @Sweet-T

You could try to use SEARCH Function to add a new column

Result = IF(SEARCH("Ready for Payment",'Table'[Column A],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column B],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column C],1,0)>0,"Ready for Payment",
IF(SEARCH("Ready for Payment",'Table'[Column A],1,0)=0,'Table'[Column A])&IF(SEARCH("Ready for Payment",'Table'[Column B],1,0)=0,'Table'[Column B])&IF(SEARCH("Ready for Payment",'Table'[Column C],1,0)=0,'Table'[Column C]))

Result 2.0 = IF(SEARCH("Ready for Payment",'Table'[Column A],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column B],1,0)>0&&SEARCH("Ready for Payment",'Table'[Column C],1,0)>0,"Ready for Payment","	Incomplete")

By the way, Whether you do not put a comma between two fields, since these are three separate columns, that will be difficult to get it.

Best Regards,

Lin

[Sweet-T]

Thanks Lin, this would work well!

[AlB]

Hi @Sweet-T

How about this for an initial version of new column 'Payment Eligibility'?

Payment Eligibility =
VAR _SearchString = "Ready for Payment"
VAR _ResultString =
    IF ( Table1[Column A] <> _SearchString, Table1[Column A] & " " )
        & IF ( Table1[Column B] <> _SearchString, Table1[Column B] & " " )
        & IF ( Table1[Column C] <> _SearchString, Table1[Column C] )
RETURN
    IF ( ISBLANK ( __ResultString ), _SearchString, __ResultString )

  The code would probably be simpler if the table was unpivoted to have a structure as:

Item	Column	Value
1	A	Ready for Payment
1	B	Ready for Payment
1	C	Ready for Payment
2	A	Ready for Payment
2	B	B Incomplete
2	C	Ready for Payment
3	A	A Incomplete
3	B	B Incomplete
3	C	Ready for Payment

[AlB · Accepted solution]

@Sweet-T

Another version, with ", " as delimiter and cleaning up the string to avoid ", " at the end

Payment Eligibility2 =
VAR _SearchString = "Ready for Payment"
VAR _ResultString =
    IF ( Table1[Column A] <> _SearchString, Table1[Column A] & ", " )
        & IF ( Table1[Column B] <> _SearchString, Table1[Column B] & ", " )
        & IF ( Table1[Column C] <> _SearchString, Table1[Column C] )
VAR _CleanResultString =
    IF (NOT ( ISBLANK ( _ResultString ) ),
        IF (RIGHT ( _ResultString, 2 ) = ", ",
            LEFT ( _ResultString, LEN ( _ResultString ) - 2 )
        ),
        _ResultString
    )
RETURN
    IF ( ISBLANK ( _CleanResultString ), _SearchString, _CleanResultString )

Code formatted with