Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
al_feror
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'

 

al_feror_2-1629127160700.png

 

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.

 

Thanks in advance for your help

1 ACCEPTED SOLUTION
al_feror
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

View solution in original post

3 REPLIES 3
al_feror
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

selimovd
Super User
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
 

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
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.