DAX RELATED function with many to many relationship

[Cado_one]

Hi all,

I know there are already many topics on this subject but I don't understand the explanations given and solutions.

I have to tables with a many to many relationship between the "code projet" columns as you can see in the following screenshot 

I need then to compare "date_insert_rpa" from first table to "DateMEX" from the second table but here comes the troubles :

RatioMW = 

CALCULATE(

SUM(rpa[nb_heure_rpa]) / (DISTINCTCOUNT(rpa[jours_date_rpa].[Year]) * [PuissanceMW]),

FILTER(rpa, 'rpa'[jours_date_rpa].[Year] > RELATED(descriptionActifsInfos[DateMEX].[Year]))

)

This error is returned : The column 'descriptionActifsInfos[DateMEX].[Year]' either doesn't exist or doesn't have a relationship to any table available in the current context.

I think other topics advise to use RELATEDTABLE instead of RELATED function but I don't understand how to use it.

If anyone have the solution I'd be very glad to try it.

Thanks in advance.

Regards,

Cado

[amitchandak]

@Cado_one , Try year functions

CALCULATE(

SUM(rpa[nb_heure_rpa]) / (DISTINCTCOUNT(year(rpa[jours_date_rpa])) * [PuissanceMW]),

FILTER(rpa, 'rpa'[jours_date_rpa].[Year] > RELATED(descriptionActifsInfos[DateMEX].[Year]))

)

[Cado_one]

Hello @amitchandak 

YEAR function can't be used inside a DISTINCTCOUNT or RELATED functions because it doesn't return a column.

[v-deddai1-msft · Accepted solution]

Hi @Cado_one ,

 If you need to get value from many side table , you need to use RELATEDTABLE function .RELATEDTABLE function needs to be used with aggregation function in your situation:

RatioMW =
CALCULATE (
    SUM ( rpa[nb_heure_rpa] )
        / ( DISTINCTCOUNT ( rpa[jours_date_rpa].[Year] ) * [PuissanceMW] ),
    FILTER (
        rpa,
        'rpa'[jours_date_rpa].[Year]
            > YEAR (
                MAXX (
                    RELATEDTABLE ( descriptionActifsInfos ),
                    descriptionActifsInfos[DateMEX]
                )
            )
    )
)

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

Best Regards,

Dedmon Dai

[Cado_one]

@v-deddai1-msft 

Thank you very much, this works perfectly !

Regards,

Cado