Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to 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", " " )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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", " " )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks - that worked.
@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 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 |
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |