Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bbwong
Helper I
Helper I

Calculated Column Flag - 2nd Condition

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,
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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", " " )

 

Fowmy_1-1622011927644.png

 

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@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", " " )

 

Fowmy_1-1622011927644.png

 

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thanks - that worked.

amitchandak
Super User
Super User

@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", " " )

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,

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

Please see below table - hope this helps.

Client IDAgreement StatusFlag
1234CurrentCurrent/Draft Agreement
2220DraftCurrent/Draft Agreement
1890Expired 
2548DraftCurrent/Draft Agreement
1567CurrentCurrent/Draft Agreement
2876Expired 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.