Euclidean Distance measure with DAX

[AlessandroFossi]

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

[Anonymous · Accepted solution]

Hi @AlessandroFossi ,

I looked at your DAX code and copied your values into Power BI and found the problem to be the following.

For this part of the image below, he needs to recreate two new tables.

You are using the NATURALINNERJOIN function, the join function 'NATURALINNERJOIN' requires at-least one common join column. So I think you can modify your code a bit.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.