Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jaymanivannan
Frequent Visitor

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.

5 REPLIES 5
MFelix
Super User
Super User

Hi @jaymanivannan,

Try this:

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

Regards,
MFelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

 

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

 OR_measure.png

 

Regards

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Dan Malagari
Consultant at Headspring

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

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.