cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dli9
Frequent Visitor

How do you add OR function to a IF/MAX measure?

My chart displays several colors and I'd like to list them in the measure, but I keep receiving an error message that says the syntax is not correct. I do not want to create one measure per color either. 

 

Scaled Volume MeasureIFMAX = IF(MAX((ColorsTable[ColorsTable])="Red", (Colorstable[ColorsTable])="Yellow"), [Raw Volume Total] * [Power BI Scale],[Raw Volume Total]))
 
Colors should include, Red, Yellow, Blue, and Orange
1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @dli9 ,

 

Create a new table contains color column and has no relationship with the original table.

Slicer table = distinct(colortable[color])

Then use this table as color slicer and modify the measure as below.

Scaled Volume Measure = 
IF(
    MAX( ColorsTable[ColorsTable]) IN VALUES('Slicer table'[color]), 
    [Raw Volume Total] * [Power BI Scale],
    [Raw Volume Total]
)

3.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Does this measure work?

=if(MAX(ColorsTable[ColorsTable])="Red"||MAX(ColorsTable[ColorsTable])="Yellow"||MAX(ColorsTable[ColorsTable])="Blue"||MAX(ColorsTable[ColorsTable])="Orange",[Raw Volume Total] * [Power BI Scale],[Raw Volume Total])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Fowmy
Super User
Super User

@dli9 

Could be this : 


Scaled Volume Measure = 
IF(
    MAX( ColorsTable[ColorsTable]) IN {"Red","Yellow", "Blue","Orange"}, 
    [Raw Volume Total] * [Power BI Scale],
    [Raw Volume Total]
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

dli9
Frequent Visitor

@Fowmy 

 

That worked! However, I'm running into the issue of all my colors being scaled now when I filter by All Colors. For example, if I filter color by Green and Scale by 1.5, I want to see the bar chart for green move whilst the rest remain the same. I also don't want a bar graph that only shows Green. 

 

dli9_0-1629492401185.png

 

 

v-jayw-msft
Community Support
Community Support

Hi @dli9 ,

 

Create a new table contains color column and has no relationship with the original table.

Slicer table = distinct(colortable[color])

Then use this table as color slicer and modify the measure as below.

Scaled Volume Measure = 
IF(
    MAX( ColorsTable[ColorsTable]) IN VALUES('Slicer table'[color]), 
    [Raw Volume Total] * [Power BI Scale],
    [Raw Volume Total]
)

3.PNG

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

dli9
Frequent Visitor

@v-jayw-msft 

 

Great, this works. If I filtered by one color and only want to see that color, would I just replace Slicertable [color] with Colorstable[color]. Using the example earlier, if I filter color by Green and Scale by 1.5, I want to ONLY want to see green with the scaled number. 

 

Scaled Volume Measure = 
IF(
    MAX( ColorsTable[ColorsTable]) IN VALUES('ColorsTable[ColorsTable]), 
    [Raw Volume Total] * [Power BI Scale],
    [Raw Volume Total]
)

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors