Relationship with direct and intermediate tables

[datobi]

Hi,

I'm a newbie in Power Bi.

I have three tables:

1. payment transactions with:
   • transaction date (9-Mar-23)
   • currency (USD)
   • amount
   • Other fields
2. forex table
   • start of the month (1-Mar-23)
   • currency (USD)
   • rate (3.7)
3. dynamic calendar
   • date (9-Mar-23)
   • start of the month (1-Mar-23)

I can't create a relationship between (payment transaction table to forex table using currency parameter) and (payment transaction table to calendar table to forex table using payment transaction date to calendar date and then calendar start of the month to forex start of the month ) 

I can do using a merge queries with (power query/editor) by creating a new column in payment transaction.

Is this even possible? Or should I just get start of month in transaction table and then do a combine value?

[datobi]

Thanks, it is working.

I noticed that doing the calculation is slower than adding merge queries, is it normal?

[johnt75]

Yes, when doing a lookup like this across a large dataset it will be slower using a measure. If performance becomes a problem then you could use the LOOKUPVALUE code to instead add a calculated column to the payment authorization table. That would increase the amount of time taken to load and refresh the data, but users would never see that, and it would make the measure very quick.

[johnt75 · Accepted solution]

I would use LOOKUPVALUE inside a measure, e.g.

Converted amount =
SUMX (
    'Payment_Authorization',
    VAR ExchRate =
        LOOKUPVALUE (
            'forex'[rate],
            'forex'[currency], 'Payment_Authorization'[currency],
            'forex'[period], RELATED ( 'Calendar'[Start of month] )
        )
    RETURN
        'Payment_Authorization'[Amount] * ExchRate
)