topic Re: Should I even use firstnonblank? in DAX Commands and Tips

Should I even use firstnonblank?

Anonymous — Tue, 23 Feb 2021 14:45:37 GMT

I'm using the approach below to filter a table for a specific value (knowing the result is unique) by using Firstnonblank.
Are there any approaches that are more effecient to achieve the same? I'm checking if the line returned contains the value "vrijgegeven" in the particular column.

Re: Should I even use firstnonblank?

Anonymous — Tue, 23 Feb 2021 15:42:44 GMT

For optimization of LASTNONBLANK and similar functions, please see Optimizing LASTNONBLANK and LASTNONBLANKVALUE calculations - SQLBI.

Re: Should I even use firstnonblank?

AmedeoM — Thu, 25 Feb 2021 22:55:07 GMT

Hello,

if you're 100% sure the result will be unique with those filters, you could use another aggregation as well (or no aggregation at all):

CALCULATETABLE ( VALUES/DISTINCT/MIN/MAX ( VrijgaveCompleetheid[crc06_vrijgavestatus] ), <filters> ) = "Vrijgegeven"

This should work as well, without the need of using FIRSTNONBLANK.

However, be aware of the fact that VALUES will raise an error in the result is not unique (i.e. if you have two or more rows as a result). In that case, an aggregator like MIN/MAX would do the trick

Please let me know if this helps 🙂

Re: Should I even use firstnonblank?

Anonymous — Fri, 26 Feb 2021 07:01:22 GMT

Thanks for sharing your approach Amedeo, this works indeed.

I assuming for now that using firstnonblank in this particular case is not "wrong". I actually felt that there must me be a more suitable approach for this simple task.