Forum Discussion

Chandrashekar's avatar
Chandrashekar
Resolver III
7 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
    7 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 )

     

cengizhanarslan's avatar
cengizhanarslan
Super User
7 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 )

 

Chandrashekar's avatar
Chandrashekar
Resolver III
7 months ago

Hello,

 

I tried but am getting 0.

 

Regards,

Chandrashekar B

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 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
      7 months ago

      Hello, Thank you it is working fine.

       

      Regards,

      Chandrashekar B