Calculated Column Flag - 2nd Condition

[bbwong]

I'm using the below with success however would like to add a 2nd condition to Agreement Status.

How can I add a 2nd condition of Agreement Status of "Draft" to share the same "Current/Draft Agreement" flag?

CURRENT/DRAFT AGREEMENT FLAG =

VAR _A =

CALCULATE (

MAX ( 'Table 1'[Client Id] ),

FILTER ( ALLEXCEPT ( 'Table 1', 'Table 1'[Client Id] ), 'Table 1'[Agreement Status]= "Current" ))

RETURN

IF ( _A <> BLANK (), "Current/Draft Agreement", " " )

With thanks,

[Fowmy · Accepted solution]

@bbwong 

Try the following measure:

CURRENT/DRAFT AGREEMENT FLAG = 
VAR _A =
CALCULATE (
MAX ( 'Table3'[Client Id] ),
FILTER ( ALLEXCEPT ( 'Table3', 'Table3'[Client Id] ), 'Table3'[Agreement Status] IN {"Current","Draft"} ))
RETURN
IF ( _A <> BLANK () && HASONEVALUE(Table3[Client ID]), "Current/Draft Agreement", " " )

[bbwong]

Thanks - that worked.

[amitchandak]

@bbwong , Try like

CURRENT/DRAFT AGREEMENT FLAG =
VAR _A =
CALCULATE (
MAX ( 'Table 1'[Client Id] ),
FILTER ( 'Table 1', 'Table 1'[Agreement Status]= "Current" ), ALLEXCEPT ( 'Table 1', 'Table 1'[Client Id] ))
RETURN
IF ( _A <> BLANK (), "Current/Draft Agreement", " " )

[bbwong]

Thanks for the quick response, the suggest calculated column is great to pick up where Agreement Status = Current.

However, it's not also flagging where Agreement Status = Draft.

Cheers,

[amitchandak]

@bbwong , Not very clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[bbwong]

Please see below table - hope this helps.

Client ID	Agreement Status	Flag
1234	Current	Current/Draft Agreement
2220	Draft	Current/Draft Agreement
1890	Expired
2548	Draft	Current/Draft Agreement
1567	Current	Current/Draft Agreement
2876	Expired