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 planid=related(planid). Is there a way to get it without calctable.

 

condition:

if cdate<=Monthlydate and st="open"  then update 1 for the entire planid (all 3 rows of planid=1)

 

id,cdate,Monthlydate,st, calccolumn

1,01/13/2017,01/31/2017,open,1

1,01/13/2017,02/28/2017,closed,1

1,01/13/2017,03/31/2017,closed,1

4,01/13/2017,04/30/2017,closed,0

 

Thank you.

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
)

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    just for my understanding, i try to rephrase your requirement

     

    • even if row 2 and 3 do not satisfy the condition st = open, the value for the calculated column is 1 because row 1 satisfies this condition
    • if one row of a group (grouped by id) satisfies the condition, the value for all rows in the group becomes 1

    Is my understanding correct?

     

    • 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