topic Re: IF condition returns DAX error in DAX Commands and Tips

IF condition returns DAX error

Anonymous — Mon, 14 Oct 2019 13:50:30 GMT

Below is a sample of the IF statements returning an error. The error is below. Also, I have tried using a Switch stmt and still get the error. Can anyone tell me what is wrong? When I use MAX it works but I am not aggregating anything in a string field.

MeasureTop = IF('temp'[Category]= "Anes Timeout %", divide(SUM(temp[num]),SUM(temp[den])*.01) )

ERROR: A single value for column 'Category' in table 'temp' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Re: IF condition returns DAX error

Nathaniel_C — Mon, 14 Oct 2019 14:18:46 GMT

Hi Anonymous ,

What do you mean you are not aggregating anything in a string field?

Nathaniel

Re: IF condition returns DAX error

Nathaniel_C — Mon, 14 Oct 2019 14:28:37 GMT

Hi Anonymous ,

I think I see what you mean. When you write a formula in a calculated column the engine knows which row you are on. However when you write a measure, the engine does not know where you are applying the measure until you do. Therefore it will not accept the formula. However if you add a max, it will know to which value to use in this formula, the (in this case) maximum value. Then it knows whether to return a true or false. Then when you apply this to for instance a matrix, it looks a the row filter and can apply it to that value or apply it to once again using the maximum when you are doing the total because there is no filter then.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: IF condition returns DAX error

Anonymous — Mon, 14 Oct 2019 15:06:54 GMT

Thanks for your help. Let me be more clear on my request. I am looking for another way to write this in DAX where I do not have to use the MAX function or is MAX correct for this? This is a common query.

If my field value = XXXX then do this formula

if my field value = YYYY then do this formula

It does not seem right having to use the MAX function but maybe it is???

Re: IF condition returns DAX error

Nathaniel_C — Mon, 14 Oct 2019 15:24:19 GMT

Anonymous ,

Yes MAX() is correct. You are asking the engine to make a decision based on your field value, yes? How does DAX "know" which row in that field is the one to use? Row 1? Row 2? etc.

Until you place it on a something that filters all the values down to one, you are trying to give it multiple values. However when you place it on a matrix, there is only one value, the value from that row.

Let me recommend Supercharge Power BI by MattAllington

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel