Forum Discussion
Cross Table Calculations
Hi 87Happy ,
Please follow these steps:
(1) Create a new measure
RATES =
VAR _O =
MAX ( 'Table 1 - Release'[ZONE] )
VAR _C =
MAX ( 'Table 1 - Release'[CITY] )
VAR _D =
MAX ( 'Table 2 - Empty'[ZONE] )
RETURN
MAXX (
FILTER (
ALL ( 'Table 3 - Rates' ),
'Table 3 - Rates'[ORIGIN] = _O
&& 'Table 3 - Rates'[DESTINATION] = _D
&& 'Table 3 - Rates'[CITY] = _C
),
'Table 3 - Rates'[RATE]
)
(2)Final output
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It's giving me an error that for a single value for column 'Table 2 - Empty [Zone] cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.
This ties to my problem that an order has one city, one origin point but could have many different destination points. I need to run the calculation for the # of matching destination points and output just the cheapest option. But as noted, factor in that I'll need to include in the visual a representation.
If I was doing this with another language, I'd run a For loop for the # of matching orders and output each calculation into a variable, checking each time whether the new output is smaller than the last output. if it is, keep it, if not discard. Then the final output would be that variable's final result.
Is that feasible in Power BI?
