Forum Discussion

Chandrashekar's avatar
Chandrashekar
Resolver III
6 months ago
Solved

Column Formula : Based on condition

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 - ...
  • cengizhanarslan's avatar
    cengizhanarslan
    6 months ago

    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's avatar
Chandrashekar
Resolver III
6 months ago

Hello,

 

I tried but am getting 0.

 

Regards,

Chandrashekar B

cengizhanarslan's avatar
cengizhanarslan
Super User
6 months ago

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's avatar
    Chandrashekar
    Resolver III
    6 months ago

    Hello, Thank you it is working fine.

     

    Regards,

    Chandrashekar B