Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Question on calculated column

Hi,

I have a table contans customerId, Code columns. I want to add calculated column and populate either ture or false.

 

The condition is if any customer contains code C or D, then i need to add value true for all records associated to that customerId else false.

Below is the sample data. you will see false for all records  for customer id 102 as no C or D code records for 102.

 

Could you please help me to resolve the issue?

 

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Anonymous ,

    You could create a new column like the below:

    Column =
IF (
    CONTAINS (
        FILTER ( Customer, Customer[CustomerID] = EARLIER ( Customer[CustomerID] ) ),
        Customer[Code], "C"
    )
        = TRUE ()
        || CONTAINS (
            FILTER ( Customer, Customer[CustomerID] = EARLIER ( Customer[CustomerID] ) ),
            Customer[Code], "D"
        )
            = TRUE (),
    TRUE (),
    FALSE ()
)

    Output result:

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, 

    Thak you for the quick response.

    I want to have true or false for all rows for a  customer ID. That means I have to update false for all records realed to 102 and update true for all records related to 100 and 101 as they have C or D records.
     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I think i am able to resolve the issue using below DAX. But i am not sure this is the efficient way or not.

      Contains C Or D =
      VAR payeeId = 'Table'[CustomerId]

      var result = CALCULATE(
      Max('Table'[CustomerId]),
      'Table'[Code] IN {"C", "D"}
      && 'Table'[CustomerId] = payeeId
      )

      return IF(result = BLANK(), FALSE(), TRUE())
  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    You could create a new column like the below:

    Column =
IF (
    CONTAINS (
        FILTER ( Customer, Customer[CustomerID] = EARLIER ( Customer[CustomerID] ) ),
        Customer[Code], "C"
    )
        = TRUE ()
        || CONTAINS (
            FILTER ( Customer, Customer[CustomerID] = EARLIER ( Customer[CustomerID] ) ),
            Customer[Code], "D"
        )
            = TRUE (),
    TRUE (),
    FALSE ()
)

    Output result:

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien