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 - 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

Column2Column2Column3Expected Output
S1S1-Adad1
S1S1-Dede1
S2S2-Adad0
S2S2-SeSe0
S4S4-adad1
S4S4-SeSe1
S4S4-dede1


Regards,
Chandrashekar B

  • 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 )

     

4 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 months ago

    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 )

     

      • 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 )