Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 , 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]))
)
Hello @amitchandak
YEAR function can't be used inside a DISTINCTCOUNT or RELATED functions because it doesn't return a column.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |