cancel
Showing results for
Did you mean:
New Member

## FIRSTNONBLANK error with measures

Hi all,

I had an issue where for a LOOKUPVALUE calculation I was getting an error in which multiple values were provided where a single value was expected. A pretty common one I would say.

Browsing this forum I found that the way to avoid this issue is using the FIRSTNONBLANK formula with a FILTER, however the calculation doesn't seem to be working.

I have a dataset with multiple time series providing information on various types of delays/durations, let's call it 'Delay Table'.

These delays and durations are then added up in a column inside the dataset. Let's call it 'Total Delay'

For that 'Total Delay' column, I have created a measure that gives me its Maximum value using the following formula

Max Total Delay = MAXX ('Delay Table,'Delay Table'[Total Delay])

The next step I'm trying to achieve is to decompose that Max Total Delay into the individual elements that form that delay.

Let's say it's constituted by Arrival Delay and Departure Delay.
In order to get these figures I'm applying the following expressions.

Arrival Delay at Max Total Delay = CALCULATE(
FIRSTNONBLANK('Delay Table'[Arrival Delay], TRUE()),
FILTER(ALL('Delay Table'),'Delay Table'[Total Delay]=[Max Total Delay])
)

Departure Delay at Max Total Delay = CALCULATE(
FIRSTNONBLANK('Delay Table'[Departure Delay], TRUE()),
FILTER(ALL('Delay Table'),'Delay Table'[Total Delay]=[Max Total Delay])
)

I cannot find any issue with the formulas above, but when I try to plot these values in a bar chart, the chart is not displaying anything. Because of this I'm assuming the calculation is no being performed correctly.

1 ACCEPTED SOLUTION
New Member

Hi @selimovd ,

I managed to solve it by including the MAXX function insde the filter, for some reason the formula I had written wasn't picking up the measure name. This is what it worked for me

Arrival Delay at Max Total Delay = CALCULATE(
FIRSTNONBLANK('Delay Table'[Arrival Delay], TRUE()),
FILTER(ALL('Delay Table'),'Delay Table'[Total Delay]=MAXX ('Delay Table,'Delay Table'[Total Delay]))
)

Departure Delay at Max Total Delay = CALCULATE(
FIRSTNONBLANK('Delay Table'[Departure Delay], TRUE()),
FILTER(ALL('Delay Table'),'Delay Table'[Total Delay]=MAXX ('Delay Table,'Delay Table'[Total Delay]))
)

I'll keep your input in mind for the next time to get a tidier solution

3 REPLIES 3
New Member

Hi @selimovd ,

I managed to solve it by including the MAXX function insde the filter, for some reason the formula I had written wasn't picking up the measure name. This is what it worked for me

Arrival Delay at Max Total Delay = CALCULATE(
FIRSTNONBLANK('Delay Table'[Arrival Delay], TRUE()),
FILTER(ALL('Delay Table'),'Delay Table'[Total Delay]=MAXX ('Delay Table,'Delay Table'[Total Delay]))
)

Departure Delay at Max Total Delay = CALCULATE(
FIRSTNONBLANK('Delay Table'[Departure Delay], TRUE()),
FILTER(ALL('Delay Table'),'Delay Table'[Total Delay]=MAXX ('Delay Table,'Delay Table'[Total Delay]))
)

I'll keep your input in mind for the next time to get a tidier solution

Super User

Hey @al_feror ,

I'm not sure if you understood or really want FIRSTNONBLANK. Check the function again and what it's doing:

FIRSTNONBLANK – DAX Guide

I would just go for MIN():

``````Arrival Delay at Max Total Delay =
CALCULATE(
MIN( 'Delay Table'[Arrival Delay] ),
FILTER(
ALL( 'Delay Table' ),
'Delay Table'[Total Delay] = [Max Total Delay]
)
)``````

In this context it also doesn't consider BLANKs as you would suggest.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Super User

Hey @al_feror ,

did it work with the Measure I provided?

I'm curious if you could solve this issue 🙂

If my approach helped to make it work, I would be happy if you could mark it as solution ✔️. Like this, the next person who is stumbling across this post can see immediately what worked, without trying all the approaches for themselves.

If you need any help please let me know.

Best regards
Denis

Announcements

#### Launching new user group features

Learn how to create your own user groups today!