Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to create a new calculated column called Inforce by contract group using the following data sample, I have added that column to showcase expected result. These are the conditions for calculated column: If Inforce Flag colunn value is "Inforce" for a specific Contract Group during specific Priod then repeat "Inforce" for all rows of that Contract Group and Period combination Else "-".
I hope it is clear, Thank you in advance.
ContractNbr | Contract Group | Period | Inforce Flag | Inforce by contract group (Expected Result) |
TP0295875A-2014-1 | TP0295875A | 202012 | - | Inforce |
TP0295875A-2015-1 | TP0295875A | 202012 | - | Inforce |
TP0295875A-2016-2 | TP0295875A | 202012 | - | Inforce |
TP0295875A-2017-2 | TP0295875A | 202012 | - | Inforce |
TP0295875A-2018-2 | TP0295875A | 202012 | - | Inforce |
TP0295875A-2019-2 | TP0295875A | 202012 | - | Inforce |
TP0295875A-2020-2 | TP0295875A | 202012 | Inforce | Inforce |
TP0295875A-2014-1 | TP0295875A | 202103 | - | - |
TP0295875A-2015-1 | TP0295875A | 202103 | - | - |
TP0295875A-2016-2 | TP0295875A | 202103 | - | - |
TP0295875A-2017-2 | TP0295875A | 202103 | - | - |
TP0295875A-2018-2 | TP0295875A | 202103 | - | - |
TP0295875A-2019-2 | TP0295875A | 202103 | - | - |
TP0295875A-2020-2 | TP0295875A | 202103 | - | - |
DS4586324D-2014-1 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2015-1 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2016-2 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2017-2 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2018-2 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2019-2 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2020-2 | DS4586324D | 202203 | - | Inforce |
DS4586324D-2021-2 | DS4586324D | 202203 | Inforce | Inforce |
Solved! Go to Solution.
Hi, @Vick143
You can try the following methods.
Result =
CALCULATE ( MAX ( 'Table'[Inforce Flag] ),
FILTER ('Table',
[Contract Group] = EARLIER ( 'Table'[Contract Group] )
&& [Period] = EARLIER ( 'Table'[Period] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Vick143
You can try the following methods.
Result =
CALCULATE ( MAX ( 'Table'[Inforce Flag] ),
FILTER ('Table',
[Contract Group] = EARLIER ( 'Table'[Contract Group] )
&& [Period] = EARLIER ( 'Table'[Period] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Vick143 ,
You could create a column as below:-
expected_Column =
VAR result =
COUNTROWS (
FILTER (
'Table',
'Table'[Contract Group] = EARLIER ( 'Table'[Contract Group] )
&& 'Table'[Inforce Flag] = "Inforce" && 'Table'[Period] = EARLIER('Table'[Period])
)
)
RETURN
IF ( result > 0, "Inforce", "-" )
Below is the file for reference
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
CalculatedColumn=MAXX(FILTER(Table,Table[Contract Group]=EARLIER(Table[Contract Group])&&Table[ Inforce Flag]="Inforce"),Table[ Inforce Flag])