topic Re: Ignoring (Blank) in a DAX query in DAX Commands and Tips

Ignoring (Blank) in a DAX query

Anonymous — Mon, 26 Oct 2020 14:56:00 GMT

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?

Re: Ignoring (Blank) in a DAX query

azaher68 — Mon, 26 Oct 2020 15:16:18 GMT

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

Re: Ignoring (Blank) in a DAX query

Anonymous — Mon, 26 Oct 2020 15:29:05 GMT

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

Re: Ignoring (Blank) in a DAX query

Anonymous — Tue, 27 Oct 2020 09:42:37 GMT

"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?

Re: Ignoring (Blank) in a DAX query

Anonymous — Tue, 27 Oct 2020 11:23:30 GMT

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.

Re: Ignoring (Blank) in a DAX query

Anonymous — Wed, 28 Oct 2020 09:45:31 GMT

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