Custom Conditional Column with IN condition

[jaymanivannan]

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.

[MFelix]

Hi @jaymanivannan,

Try this:

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

Regards,
MFelix

[jaymanivannan]

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]

@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

[malagari]

Does it need to be a string for some reason, or is that what it defaulted to?  You can change the data type for a column under the Modeling tab in Power BI Desktop.

[jaymanivannan]

Hi @malagari, Thanks for the suggestion. Yes, this should be a string. There are some other accounts with NVARCHAR values.