Forum Discussion

jaymanivannan's avatar
jaymanivannan
Frequent Visitor
9 years ago

Custom Conditional Column with IN condition

Hi, I’m trying to add multiple

I’m trying to create a custom conditional column with 1 filter column (from the table) and multiple values. In a SQL language, my condition is 

 

SELECT SUM(AMOUNT) FROM TABLE 

WHERE ACCOUNT IN (4100, 4200,4300)

 

Power BI Conditional column allows only one value. 

 

Please let me know is there a way to create a conditional column instead of a Measure with the above requirement?

 

Thanks.

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago
    Hi jaymanivannan,

    Try this:

    Measure = CALCULATE (SUM(Table[Amount]); Table[Account]=4100 && Table[Account]=4200 && Table[Account]=4300)

    Regards,
    MFelix
    • jaymanivannan's avatar
      jaymanivannan
      Frequent Visitor
      9 years ago

      Hi MFelix, Thanks for the update. I tried this, but the problem is the Account is string character ( Ex. '00004100') not a number, so the formula is throwing an error as "The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type string".  Still I am working on this. 

       

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        jaymanivannan,

         

        No problem you can use the notation for text "" change your formula to this, also I was incorrect in the formula the operator reference was incorrect:

        && = AND

        || = OR

         

        So your formula should look like this, below is an image of the result I have reach:

         

         

        Account_Total_41_to_43 = CALCULATE(
				SUM('ACCOUNT_TOTAL'[Amount]); 
				'ACCOUNT_TOTAL'[Account]="00004100" ||
				'ACCOUNT_TOTAL'[Account]="00004200"||
				'ACCOUNT_TOTAL'[Account]="00004300"
				)

         

         

        Regards

        MFelix