Column Formula : Based on condition

[Chandrashekar]

Hello,

Need help in column formula for below table. I am looking for formula based on column2.

If Number continuous and if it contains De then only formula should get 1 else 0.

For Ex: S1 - We have two occurrence and also we have De in it. So formula should get 1.
S2 - We have two occurrence but we do not have De in it so formula should get 0

Column2	Column2	Column3	Expected Output
S1	S1-Ad	ad	1
S1	S1-De	de	1
S2	S2-Ad	ad	0
S2	S2-Se	Se	0
S4	S4-ad	ad	1
S4	S4-Se	Se	1
S4	S4-de	de	1

Regards,
Chandrashekar B

[cengizhanarslan]

I believe you are looking a DAX to create a calculated column, not nmeasure. If so, please try the formula below:

Flag =
VAR _CurrentKey = Table[Column1]

VAR _RowCount =
    CALCULATE (
        COUNTROWS ( Table ),
        Table[Column1] = _CurrentKey
    )

VAR _HasDe =
    CALCULATE (
        COUNTROWS ( Table ),
        Table[Column1] = _CurrentKey,
        CONTAINSSTRING ( LOWER ( Table[Column2] ), "de" )
    )

RETURN
IF ( _RowCount > 1 && _HasDe > 0, 1, 0 )

[Chandrashekar]

Hello,

I tried but am getting 0.

Regards,

Chandrashekar B

[cengizhanarslan · Accepted solution]

You are right, its becasue of SELECTEDVALUE(). could you try below, I just tested it, it works fine.

Flag = 
VAR _CurrentKey = 'Table'[Column1]
VAR _RowCount =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Column1] = _CurrentKey )
    )
VAR _HasDe =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Column1] = _CurrentKey
                && CONTAINSSTRING ( LOWER ( 'Table'[Column2] ), "de" )
        )
    )
RETURN 
IF ( NOT ISBLANK ( _CurrentKey ) && _RowCount > 1 && _HasDe > 0, 1, 0 )

[Chandrashekar]

Hello, Thank you it is working fine.

Regards,

Chandrashekar B