topic Calculated column with conditions in DAX Commands and Tips

Calculated column with conditions

Vick143 — Wed, 01 Feb 2023 20:07:34 GMT

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

Re: Calculated column with conditions

wdx223_Daniel — Thu, 02 Feb 2023 03:52:27 GMT

CalculatedColumn=MAXX(FILTER(Table,Table[Contract Group]=EARLIER(Table[Contract Group])&&Table[ Inforce Flag]="Inforce"),Table[ Inforce Flag])

Re: Calculated column with conditions

Samarth_18 — Thu, 02 Feb 2023 05:24:27 GMT

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

Re: Calculated column with conditions

v-zhangti — Thu, 02 Feb 2023 05:55:06 GMT

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.