Forum Discussion

datobi's avatar
datobi
Frequent Visitor
3 years ago
Solved

Relationship with direct and intermediate tables

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?

 

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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
)

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    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
)
  • datobi's avatar
    datobi
    Frequent Visitor
    3 years ago

    Thanks, it is working.

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

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      3 years ago

      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.