Forum Discussion
IF statement acting inconsistent, no FALSE option allowed
Hi all!
I've got a problem with an if statement that I can't seem to figure out. To make things worse, microsofts own documentation isn't consistent with the behavior of my if statement.
Here is the statement:
Category1CountWeek =
SUMX(
VALUES('VBM+Server'[Serial number])
, IF(([ProductionCategoryWeek] = "0 - 50"), 1))
ProductionCategoryWeek returns a string depending on a machines category (eg 0 - 50, 50 - 100, etc.). The SUMX works fine, it sums all the 1's just like it should, that isn't where the problem lies. The problem I ran into is that if there is no 1's to sum it returns BLANK, I'd rather have it return 0. My initial solution for this was just adding ', 0' as the false option:
Category1CountWeek =
SUMX(
VALUES('VBM+Server'[Serial number])
, IF(([ProductionCategoryWeek] = "0 - 50"), 1, 0))
Am I crazy? This should be perfectly fine, right? It isn't according to DAX... It says 'Unexpected expression 0'. It doesn't matter what I put as my false option, it always gives me 'Unexpected expression', that is, until I turn the True option into TRUE() (with or without brackets):
Category1CountWeek =
SUMX(
VALUES('VBM+Server'[Serial number])
, IF(([ProductionCategoryWeek] = "0 - 50"), TRUE(), 0))
This is suddenly fine again, for whatever reason, but this is where it's inconsistent with microsofts own documentation. The microsoft documentation says the following under 'Remarks':
The IF function attempts to return a single data type in a column. Therefore, if the values returned by value_if_true and value_if_false are of different data types, the IF function will implicitly convert data types to accommodate both values in the column. For example, the formula IF(<condition>,TRUE(),0) returns a column of ones and zeros and the results can be summed, but the formula IF(<condition>,TRUE(),FALSE()) returns only logical values. For more information about implicit data type conversion, see Data types supported in tabular models.
This doesn't seem to be the case for me, in my case it returns a 0 for every case that is FALSE, but it doesn't return anything for the TRUE's, no 1's, nothing.
So why can't I just use a 0 as my false option? Is there another way to get 0 instead of blank? Am I the only one that has this issue?
I'm really clueless on why this happening, any help would be greatly appreciated!
3 Replies
- sturlaws
Resident Rockstar
Hi Anonymous ,
I am not able to recreate your issue, this code works as expected in the small sample file I created for myself:
if_test = SUMX ( VALUES ( 'Table1'[Column1] ), IF ( ( [measure] > 5 ), 1, 0 ) )
Perhaps try to create a very simple model, and see if you are able to create your issue yourself. If you are, you could share the sample reportCheers,
Sturla
