Dear Community,
I found myself stuck with this formula in DAX, seems ok because the editor does not throws an error but the measure is always zero in power BI charts.
The measure is intended to calculate euclidean distance between two multi-variable time-series that you find in this table exemple:
| Fecha | Ano | Dia | Mes | pp | Original Without Suffix | Suffix | Datetime | hours |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.2932 | URAN_Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0147 | Net_imports | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0016 | NGAS_CCGT_new_Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0003 | SUN__PV____Generic | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0003 | SUN__PV____Generic | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0 | SUN__CSP___Generic | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.5017 | WIND_ONSHO_Generic | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0447 | WATR_ROR___Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0147 | WATR_STO___Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.102 | NRES_Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0148 | BIOM_Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0157 | WAST_Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0155 | BIOM_Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0 | WATR_PSP___Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0971 | NRES_Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0101 | COAL_Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0149 | WAST_Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0421 | WATR_STO___Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0106 | COAL_Aggr | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.2856 | URAN_Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0 | Net_imports | PROP | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.4778 | WIND_ONSHO_Generic | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0425 | WATR_ROR___Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0 | NGAS_CCGT_new_Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0 | WATR_PSP___Aggr | PROP_OBS | 1/1/2018 1:00 | 1:00 |
| 1/1/2018 1:00 | 2018 | 1 | 1 | 0.0001 | SUN__CSP___Generic | PROP_OBS | 1/1/2018 1:00 | 1:00 |
and the DAX measure is this:
EuclideanDistance =
VAR SelectedSuffix = "PROP" -- Replace with the desired suffix ("PROP" or "OBS")
VAR Series1Values =
ADDCOLUMNS(
SUMMARIZE(
FILTER('df (2)', 'df (2)'[Suffix] = SelectedSuffix),
'df (2)'[Datetime],
'df (2)'[Original Without Suffix]
),
"pp_Series1", CALCULATE(MAX('df (2)'[pp]))
)
VAR Series2Values =
ADDCOLUMNS(
SUMMARIZE(
FILTER('df (2)', 'df (2)'[Suffix] <> SelectedSuffix),
'df (2)'[Datetime],
'df (2)'[Original Without Suffix]
),
"pp_Series2", CALCULATE(MAX('df (2)'[pp]))
)
VAR IntersectedValues =
NATURALINNERJOIN(Series1Values, Series2Values)
VAR SumOfSquares =
SUMX(
IntersectedValues,
POWER([pp_Series1] - [pp_Series2], 2)
)
VAR EuclideanDistance =
SQRT(SumOfSquares)
RETURN
EuclideanDistance
Any idea on how to solve this?
Thanks in advance
Alessandro
