Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Is it possible to activate the relationship inside a summarize? I need to do a currency conversion for both purchase and sales so I need them both in a relationship but can't activate them both. I'm following the pattern mentioned here @ https://www.daxpatterns.com/currency-conversion/ . I am able to do it if I activate one of the relationship but I can't address the case where the other relationship is inactive.
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( Bookings, Conversion_Rate[Code] ),
"@SalesAmountInCurrency", [Sales],
"@Rate", CALCULATE ( SELECTEDVALUE ( Conversion_Rate[Rate] ) )
)
Solved! Go to Solution.
Hi @justivan ,
According to your description, for choosing USERELATIONSHIP or LOOKUPVALUE, in some cases, for USERELATIONSHIP, In fact, the context transition still operates using the original relationship and its effect needs to be removed. The advice is to never write such code, It is better to rely on a simpler version (LOOKUPVALUE), which does not use relations at all.
You can also read the following blog and similar case to deepen your understanding:
Expanded tables in DAX - SQLBI
Solved: Re: using inactive relationship - Microsoft Power BI Community
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @justivan ,
According to your description, for choosing USERELATIONSHIP or LOOKUPVALUE, in some cases, for USERELATIONSHIP, In fact, the context transition still operates using the original relationship and its effect needs to be removed. The advice is to never write such code, It is better to rely on a simpler version (LOOKUPVALUE), which does not use relations at all.
You can also read the following blog and similar case to deepen your understanding:
Expanded tables in DAX - SQLBI
Solved: Re: using inactive relationship - Microsoft Power BI Community
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I managed to get the needed result.
First I generated an aggregated table using LOOKUPVALUE ang got this.
I then wrote the measure like this. which hopefully is the correct way. To be honest I don't fully understand it but it works. 😂
Sales =
VAR __AggregatedSales =
SUMMARIZE (
ADDCOLUMNS (
Bookings,
"@Rate",
LOOKUPVALUE (
Conversion_Rate[Rate],
Conversion_Rate[Code], Bookings[Sales_Currency]
)
),
[@Rate],
"@Sales", SUM ( Bookings[SalesPrice] )
)
VAR __Result =
SUMX ( __AggregatedSales, [@Sales] * IF ( ISBLANK ( [@Rate] ), 1, [@Rate] ) )
RETURN
__Result
@justivan , In the addcolumns, when adding a new one, You should be able to use userelationship inside the calculate
Hi @amitchandak ,
Thanks for the feedback.
Yes I can use USERELATIONSHIP inside the CALCULATE but the problem is in the SUMMARIZE because there is no active relationship between the 'Bookings' table and 'Conversion_Rate' table.