Forum Discussion

hoopaz68's avatar
hoopaz68
New Member
1 year ago
Solved

Dax Help

Good Afternoon.  I'm fairly new to using DAX so bear with me.    I'm trying to use DAX to calculate invoice amounts in different currencies.  I have two example tables below in my PBI desktop.  These...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi hoopaz68 ,

     

    In the data you provided, the cruno field does not have a unique identifier and cannot be used as a condition for joining tables.
    But based on your requirement, you can refer to the following formula:

    SelectedCurrencyRate =
CALCULATE (
    MAX ( 'Currency_Table'[rate] ),
    FILTER (
        'Currency_Table',
        'Currency_Table'[cruno] = SELECTEDVALUE ( 'Invoice Table'[cruno] )
            && 'Currency_Table'[currency] = SELECTEDVALUE ( 'Currency_Table'[currency] )
    )
)

    ConvertedInvoiceAmount = 
SUMX(
    'Invoice Table',
    'Invoice Table'[inv_amt_usd] * [SelectedCurrencyRate]
)

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi hoopaz68 ,

 

In the data you provided, the cruno field does not have a unique identifier and cannot be used as a condition for joining tables.
But based on your requirement, you can refer to the following formula:

SelectedCurrencyRate =
CALCULATE (
    MAX ( 'Currency_Table'[rate] ),
    FILTER (
        'Currency_Table',
        'Currency_Table'[cruno] = SELECTEDVALUE ( 'Invoice Table'[cruno] )
            && 'Currency_Table'[currency] = SELECTEDVALUE ( 'Currency_Table'[currency] )
    )
)

ConvertedInvoiceAmount = 
SUMX(
    'Invoice Table',
    'Invoice Table'[inv_amt_usd] * [SelectedCurrencyRate]
)

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.