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.
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.