Forum Discussion

al_feror's avatar
al_feror
New Member
5 years ago
Solved

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.

 

Thanks in advance for your help

Need Help
  • al_feror's avatar
    al_feror
    5 years ago

    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

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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
     
    • selimovd's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      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
       
  • al_feror's avatar
    al_feror
    New Member
    5 years ago

    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