Userelationship inside Summarize

[justivan]

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] ) )
)

[v-henryk-mstf · Accepted 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.

[justivan]

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

[amitchandak]

@justivan , In the addcolumns, when adding a new one, You should be able to use userelationship inside the calculate

[justivan]

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.