DAX if this and this then that

[Oisdf]

Hi All,

I know there's countless threads on DAX help but I've found none that have addressed my issue, I'm new to PBI so please be patient with me, I'm sorry if this is something obvious.

Let me know what I am doing wrong here, this DAX code to create a new collumn is not working - All of these data types are whole numbers - yes this is reflected Formatting>Data type.

INVALID_FILTER = 
IF([INVALID_CALC] < "8" ,
	IF([PARAM_C] >= [INVALID_CALC], [PARAM_C], "" )
        ,
	IF(([PARAM_C]/[SITES_TESTED])*100 >= [INVALID_CALC],[PARAM_C], "" )
)

 but I keep getting this error:

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I have tried applying VALUE and FORMAT to all the values in my above script like this:

INVALID_FILTER = 
IF(VALUE([INVALID_CALC]) < "8" ,
	IF(VALUE([PARAM_C]) >= VALUE([INVALID_CALC]), VALUE([PARAM_C]), "" )
        ,
	IF((VALUE([PARAM_C])/VALUE([SITES_TESTED]))*100 >= VALUE([INVALID_CALC]),VALUE([PARAM_C]), "" )
)

but it doesnt work, instead giving this error, which is slightly different.

DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

What am I doing wrong?
Thanks for your time.

[AllisonKennedy · Accepted solution]

@Oisdf  Great question, 

You are using Quotes "" on your numbers, which converts them to text, so try:

INVALID_FILTER = 
IF([INVALID_CALC] < 8 ,
	IF([PARAM_C] >= [INVALID_CALC], [PARAM_C], "" )
        ,
	IF(([PARAM_C]/[SITES_TESTED])*100 >= [INVALID_CALC],[PARAM_C], "" )
)

[Oisdf]

Thank you @AllisonKennedy ! 
Collumn is working now, however the data is not as expected. All values are appearing as Null.

Example here, this row should not be Blank , it should be PARAM_C (10)

IF([PARAM_C] >= [INVALID_CALC], [PARAM_C], "" 

Can you spot anything wrong?

[AllisonKennedy]

@Oisdf You need to create this as a MEASURE, not a COLUMN to have it recognize the measures, hopefully this article helps explain things: https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html 

[Samarth_18]

Hi @Oisdf ,

Please try this:-

INVALID_FILTER =
IF (
    [INVALID_CALC] < 8,
    IF ( [PARAM_C] >= [INVALID_CALC], [PARAM_C], BLANK () ),
    IF (
        ( [PARAM_C] / [SITES_TESTED] ) * 100 >= [INVALID_CALC],
        [PARAM_C],
        BLANK ()
    )
)

Thanks,

Samarth