Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
Hey @al_feror ,
I'm not sure if you understood or really want FIRSTNONBLANK. Check the function again and what it's doing:
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.
Hey @al_feror ,
did it work with the Measure I provided?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |