Forum Discussion

aj1107's avatar
aj1107
Advocate I
9 years ago
Solved

calculated column logic.

I need to create a calculated column based on below condition. Not sure how to get the entire planid logic using calc column. i tried to create a calctable of below condition and then update using p...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    you can find a little example

     

    For this table:

     

    A caclculated column using this DAX statement:

    calcColumn = 
if(
    calculate(
        count('Table1'[id]),
        FILTER(
            ALLEXCEPT('Table1',Table1[id]),
            'Table1'[right] > 'Table1'[left] && 'Table1'[literal] = "yes"
        )
   ) >= 1, 
   1, 
   2
)

    Returns these values:

     

     

    Hope this helps

     

  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    aj1107,

     

    You may refer to the following DAX as well.

    calccolumn =
IF (
    COUNTROWS (
        FILTER (
            Table1,
            Table1[id] = EARLIER ( Table1[id] )
                && Table1[cdate] <= Table1[Monthlydate]
                && Table1[st] = "open"
        )
    )
        > 0,
    1,
    0
)
aj1107's avatar
aj1107
Advocate I
9 years ago

yes. if atleast one row of a group(id) satisfies then the entire group id (planid=1) is 1.

TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 

you can find a little example

 

For this table:

 

A caclculated column using this DAX statement:

calcColumn = 
if(
    calculate(
        count('Table1'[id]),
        FILTER(
            ALLEXCEPT('Table1',Table1[id]),
            'Table1'[right] > 'Table1'[left] && 'Table1'[literal] = "yes"
        )
   ) >= 1, 
   1, 
   2
)

Returns these values:

 

 

Hope this helps