Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm a newbie in Power Bi.
I have three tables:
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?
Solved! Go to 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
)
Thanks, it is working.
I noticed that doing the calculation is slower than adding merge queries, is it normal?
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.
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
)
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |