Issue with measure calculation with two tables not directly related

[Anonymous]

Hello,

I am trying to multiply a net price in one table and the exchange rate in another table. Both tables have a relationship to a date table but are not directly related. The exchange rate is the same for each currency for the year. The date value in the Order Table is dd-mm-yyyy and the date value in the Exchange Rate table is yyyy (just thought I would mention it in case it would be relevant).

I have tried to apply a measure based on a solution by @marcorusso here: https://www.sqlbi.com/blog/marco/2010/02/09/how-to-relate-tables-in-dax-without-using-relationships/ but without success.

My measure

Net USD =
SUMX (
    'SFCC Orders',
    'SFCC Orders'[Net Price]
        * CALCULATE (
            VALUES ( 'Exchange Rate'[Exchange Rate] ),
            FILTER (
                'Exchange Rate',
                'Exchange Rate'[country_key] = 'SFCC Orders'[country_key]
                    && 'Exchange Rate'[Year] = 'SFCC Orders'[SFCC Order Date]
            )
        )
)

In a visual I can see that the values appear correct in the basic context. i.e Net Price and exchange rate appear correct by country and year.

However when I create the measure to multiply net price by exchange rate it appears as though the context is incorrect (at least for what I need!) See below.

Is there a way to get this to work with the relationships to the date table? Any help with clarifying how I could correctly calculate this would be great.

Thanks

[marcorusso]

Assuming you have a Date table (you should have one, otherwise you have many other issues).

Net USD =
SUMX (
    SUMMARIZE ( 'SFCC Orders', 'Date'[Year], 'SFCC Orders'[country_key] ),
    CALCULATE ( SUM ( 'SFCC Orders'[Net Price] ) )
        * CALCULATE ( 
            SELECTEDVALUE ( 'Exchange Rate'[Exchange Rate] ),
            TREATAS ( 
                { 'Date'[Year], 'SFCC Orders'[country_key] },
                'Exchange Rate'[country_key],
                'Exchange Rate'[Year]
            )
        )
)

[Anonymous]

Hello Marco,

Many thanks for your reply. I just finished your introductory courses on modeling and DAX and it has helped me immensely, they are really insightful and well produced. Will be signing up for the more advanced courses as soon as I feel confident enough that I have a good grip on the basics.

In relation to your response, yes I have a Date table. However, when I create the measure I get a message stating "In the function TREATAS the number of output columns (2) does not match the number of columns in the input table (1).

Do i understand correctly that the input fields are:

{ 'Date'[Year], 'SFCC Orders'[country_key] },

Is the error due to the 'Date'[Year] and 'SFCC Orders'[country_key] residing in different tables?

Thank you!

[marcorusso]

Sorry - this is the fixed code:

Net USD =
SUMX (
    SUMMARIZE ( 'SFCC Orders', 'Date'[Year], 'SFCC Orders'[country_key] ),
    CALCULATE ( SUM ( 'SFCC Orders'[Net Price] ) )
        * CALCULATE ( 
            SELECTEDVALUE ( 'Exchange Rate'[Exchange Rate] ),
            TREATAS ( 
                { ( 'Date'[Year], 'SFCC Orders'[country_key] ) },
                'Exchange Rate'[country_key],
                'Exchange Rate'[Year]
            )
        )
)

[Anonymous]

Hi Marco,

The measure is now not causing any error but it only outputs blank rows. I'm not clear on why that might be.

Below is a link to a pbix file with a reduced set of tables and the measure. If you have some time to review it would be greatly appreciated.

https://drive.google.com/file/d/1ovwDdxiSj1hCw4Bm0UR96hdpyH4BX8dY/view?usp=sharing

Many thanks for your reply and time.

[marcorusso · Accepted solution]

There was an inversion of two arguments in TREATAS (my fault) and you stored the year as a date instead of as an integer (your fault). Even money 😁

Net USD = 
SUMX (
    SUMMARIZE ( 'Test Orders', 'Dates'[Year], 'Test Orders'[country_key] ),
    CALCULATE ( SUM ( 'Test Orders'[Net Price] ) )
        * CALCULATE ( 
            SELECTEDVALUE ( 'Exchange Rate'[Exchange Rate] ),
            TREATAS ( 
                { ( 'Test Orders'[country_key], DATE ( 'Dates'[Year], 1, 1 ) ) },
                'Exchange Rate'[country_key],
                'Exchange Rate'[Year]
            )
        )
)

[Anonymous]

I'll take that!  🤣 👍

Many thanks.