ERROR - "the expression contains columns from multiple tables, but only columns from a single table.

[anonymous_98]

ERROR - "the expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

Here is the code sample for which I am getting the above error. 
can anyone please help why am getting this error?

Top Purchaser Sales = 
    VAR _CurrentTopPurchaserCountry = 
        MAX(Customers[Country])

    VAR _CurrentProductID = 
        MAX('Online Sales'[Product Key])

    VAR _Sales = 
        CALCULATE(
            [Sales Amount],
                'Online Sales'[Product Key] = _CurrentProductID &&
                Customers[Country] = _CurrentTopPurchaserCountry
        )

    RETURN
        _Sales

[Pragati11]

Hi @anonymous_98 ,

Can you try the following DAX:

Top Purchaser Sales = 
    VAR _CurrentTopPurchaserCountry = 
        MAX(Customers[Country])

    VAR _CurrentProductID = 
        MAX('Online Sales'[Product Key])

    VAR _Sales = 
        CALCULATE(
            [Sales Amount],
                'Online Sales'[Product Key] = _CurrentProductID &&
                RELATED(Customers[Country]) = _CurrentTopPurchaserCountry
        )

    RETURN
        _Sales

I am assuming there is an active relationship between both tables used in the your DAX expression.

Thanks,

Pragati

[anonymous_98]

Hey @Pragati11 ,

Thanks for your help.
I was able to fix the issue. 
Tried your code but it is not working the reason is the RELATED function needs to be used inside a FILTER function for it to work.

[DimaMD]

HI @anonymous_98 
As far as I understand you want to know the top customer, 
You need to choose "MAX" from 1 sales table
You choose the maximum country and the maximum unrelated product
If you want to know the top buyer you need to use the TOPN or RANX function

[anonymous_98 · Accepted solution]

Hey,

Here is the fix for your error
Before Change:

Top Purchaser Sales = 
    VAR _CurrentTopPurchaserCountry = 
        MAX(Customers[Country])

    VAR _CurrentProductID = 
        MAX('Online Sales'[Product Key])

    VAR _Sales = 
        CALCULATE(
            [Sales Amount],
                'Online Sales'[Product Key] = _CurrentProductID &&
                Customers[Country] = _CurrentTopPurchaserCountry
        )

    RETURN
        _Sales

After Change:

Top Purchaser Sales = 
    VAR _CurrentTopPurchaserCountry = 
        MAX(Customers[Country])

    VAR _CurrentProductID = 
        MAX('Online Sales'[Product Key])

    VAR _Sales = 
        CALCULATE(
            [Sales Amount],
            FILTER(
                'Online Sales',
                'Online Sales'[Product Key] = _CurrentProductID &&
                RELATED(Customers[Country]) = _CurrentTopPurchaserCountry
            )
        )

    RETURN
        _Sales

So why did I get this error in the first place?

1. Under the Filter argument, you are using columns from 2 different table
2. Both the columns are from two different table so you can not directly use them to filter [Sales Amount] measure
3. Country column is from Customer Table and Product Key is from Online Sales Table

So what should we do when we need to filter based on columns from 2 different tables?

1. Use FILTER inside an CALCULATE function
2. Use RELATED inside the FILTER to filter using the column of another related table.