Ignoring (Blank) in a DAX query

[Anonymous]

Hey PBI Community. I'm close to the end of a long project, but have one final issue with a Dax query that I am using to determine some values, seen below:

ExceedsCheck2 = 
VAR ThisResult =
    MAX ('Query1'[StdResult])
VAR Criteria=
    MAXX (
        ALLSELECTED ( 'Query1'[GAC] ),
        CALCULATE (
            MAX ( 'Query1'[GAC] )
        )
    )
VAR Range=
    MAXX (
        ALLSELECTED ( 'Query1'[lod] ),
        CALCULATE (
            MAX ( 'Query1'[lod] )
        )
    )
RETURN
    if (Range>Criteria, 1,if(ThisResult>=Criteria, 2, 0))

This effectively checks a column (LOD) against another (GAC) to see if it exceeds the value within. The problem I have, is that when this is checking the values, it can see that there are blanks within the data which I can't ignore as it isn't my data and can include blanks whenever it comes in.

How can I tweak the query above, to only look at values that contain a number?

[Anonymous]

"The problem I have, is that when this is checking the values, it can see that there are blanks within the data which I can't ignore as it isn't my data and can include blanks whenever it comes in."

Can you please give a more clear description of where in your formula the problem occurs?

[Anonymous]

Hi @Anonymous 

Effectively, the data-set has a GAC and LOD column. Both of these have numeric values and (Blank) values. The formula needs to only check if the value is numeric, if it isn't then mark it as 3.

For example, I have a result of 0.20 and the GAC is (Blank) so the formula sees this as Result > GAC and highlights is as such, which is incorrect. The forumla should see (Blank) and mark it as 3 or just ignore it, it doesn't do this presently and I am not sure how to achieve this.

[Anonymous · Accepted solution]

I've managed to get this to work using NOT ISBLANK and ISBLANK checks within DAX

[azaher68]

Is it a problem for you if you replace null values with 0?

[Anonymous]

@azaher68 thanks for your reply. Yes, this still causes problems because the result will still be greater than 0 and flag it.