Forum Discussion

Vick143's avatar
Vick143
Frequent Visitor
3 years ago
Solved

Calculated column with conditions

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.

 

ContractNbrContract GroupPeriodInforce FlagInforce by contract group (Expected Result)
TP0295875A-2014-1TP0295875A202012-Inforce
TP0295875A-2015-1TP0295875A202012-Inforce
TP0295875A-2016-2TP0295875A202012-Inforce
TP0295875A-2017-2TP0295875A202012-Inforce
TP0295875A-2018-2TP0295875A202012-Inforce
TP0295875A-2019-2TP0295875A202012-Inforce
TP0295875A-2020-2TP0295875A202012InforceInforce
TP0295875A-2014-1TP0295875A202103--
TP0295875A-2015-1TP0295875A202103--
TP0295875A-2016-2TP0295875A202103--
TP0295875A-2017-2TP0295875A202103--
TP0295875A-2018-2TP0295875A202103--
TP0295875A-2019-2TP0295875A202103--
TP0295875A-2020-2TP0295875A202103--
DS4586324D-2014-1DS4586324D202203-Inforce
DS4586324D-2015-1DS4586324D202203-Inforce
DS4586324D-2016-2DS4586324D202203-Inforce
DS4586324D-2017-2DS4586324D202203-Inforce
DS4586324D-2018-2DS4586324D202203-Inforce
DS4586324D-2019-2DS4586324D202203-Inforce
DS4586324D-2020-2DS4586324D202203-Inforce
DS4586324D-2021-2DS4586324D202203InforceInforce
"Calculated Column"
"DAX"
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

     

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

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

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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.