calculated column logic.

[aj1107]

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.

[TomMartens · Accepted solution]

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

[TomMartens]

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]

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

[v-chuncz-msft · Accepted solution]

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

[TomMartens · Accepted solution]

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