Calculated measure from 2 columns error...ok with 1 variable, just not 2.

[unclejemima]

Here is my attempted formula...

Income_55a_HotDrinks = CALCULATE(
	[Income4000'sCalc],
		'Calendar'[IsYesterday] = "True" && Inventry[Size_3] = "Hot")

I can get this calculation to work if there is only one variable (examle, Calendar[isYesterday]

But as soon as I put 2 variables in it gives me the error...

The expression contains multiple columns, but onl a single column can be used in a true/false expression that is used as a table filter expression.

Any suggestions as to what formula I should be using to do this?  Perhaps one with "SEARCH" in it instead?

Let me know 😉

[parry2k · Accepted solution]

I assume you have relationship between calendar and inventory tables and the filter you are applying on are columns (not measure)

Income_55a_HotDrinks = CALCULATE(
	[Income4000'sCalc],
		'Calendar'[IsYesterday] = "True" ,  Inventry[Size_3] = "Hot")

[Anonymous]

Can you post the formula you are trying to build, which throws error?

Thanks
Raj

[unclejemima]

Thanks rajendran

First I tried this...

Income_55a_HotDrinks = CALCULATE(
	[Income4000'sCalc],
		'Calendar'[IsYesterday] = "True" && Inventry[Size_3] = "Hot")

get error.  Then this...(below) and get error.

Income_55a_HotDrinks = CALCULATE(
	[Income4000'sCalc],
		SEARCH("True",'Calendar'[IsYesterday],,blank() 
            && SEARCH("Hot",Inventry[Size_3],,blank())))

What I'm trying to do is, if Calendar[IsYesterday] = "True" AND Inventry[Size_3) = "Hot", then calculate the Income4000'sCalc

[parry2k · Accepted solution]

I assume you have relationship between calendar and inventory tables and the filter you are applying on are columns (not measure)

Income_55a_HotDrinks = CALCULATE(
	[Income4000'sCalc],
		'Calendar'[IsYesterday] = "True" ,  Inventry[Size_3] = "Hot")

[unclejemima]

Thanks parry2k.  That appears to be the answer.  Marked as such 🙂

[unclejemima]

I came up with another situation I need to an OR in.

Income_55a_ColdDrinks_Today = CALCULATE(
 [Income4000'sCalc],
 'Calendar'[IsToday] = "True" , Inventry[Size_3] = "Ice" OR "Gel")

If Inventry[Size_3] = "Ice" OR "Gel"...how would I go about doing that?

My formula does not work.  Suggestions???

[unclejemima]

This might work???

Income_55a_ColdDrinks_Today = CALCULATE(
	[Income4000'sCalc],
		'Calendar'[IsToday] = "True" , OR (Inventry[Size_3] = "Gel", Inventry[Size_3] = "Ice"))

[unclejemima]

Got this to work...but now if I want 3 OR statements, the IF(Or) does not work.

55a_ColdDrink = if(OR(Inventry[Size_3]="Ice",Inventry[Size_3]="Gel"),"Cold","")

Now I want to also add OR Inventry[size_3)= "IceFog" as well...but max arguments for OR in Power BI is 2...

help please 😉

[parry2k]

you can have multiple OR by using || 

55a_ColdDrink = if(Inventry[Size_3]="Ice" || Inventry[Size_3]="Gel" ,"Cold","")

[unclejemima]

55a_ColdDrink = IF(Inventry[Size_3] = "Ice"
    && Inventry[Size_3]="Gel"
    || Inventry[Size_3]="IceFog","Cold",blank())

Using this I'm getting close...but only returns "Cold" for "IceFog", not the "Gel" or "Ice"

What am I missing in my formula???

[parry2k · Accepted solution]

you have && condition with or, what actually you are looking for

if you need or then replace && with ||

[unclejemima]

This did it 🙂

55a_ColdDrink = IF(Inventry[Size_3] = "Ice"
    || Inventry[Size_3]="Gel"
    || Inventry[Size_3]="IceFog","Cold",blank())

Thank you!!!!