Can't Select Columns from other tables in DAX

[Niels_T]

I have the following DAX-formula.

The problem is I can't select any of the columns of the tables where I want to get my data from.

 

Total Direct Cost (Excl. VAT) =

IF('Vendor List'[EU / Non-EU] = "non-EU (EUR)",

'Stock Forecast'[Total Direct Cost (Excl. Last Unit Cost) (Excl. VAT)]*1,06,

IF('Vendor List'[EU / Non-EU] = "non-EU (USD)", 'Stock Forecast'[Total Direct Cost (Excl. Last Unit Cost) (Excl. VAT)]*1,35/CALCULATE('Currency Rate'[EUR per unit],'Currency Rate'[Currency] = "USD"),

IF('Vendor List'[EU / Non-EU] = "non-EU (USD)", 'Stock Forecast'[Total Direct Cost (Excl. Last Unit Cost) (Excl. VAT)]*1,03

)))

[v-henryk-mstf · Accepted solution]

Hi @Niels_T ,

It seems that there is a problem with the expression relationship between the if statements (","), which works fine with the following formula:

Total Direct Cost (Excl. VAT) =
IF (
    'Vendor List'[EU / Non-EU] = "non-EU (EUR)",
    'Stock Forecast'[Total Direct Cost (Excl. Last Unit Cost) (Excl. VAT)] * 1,
    06
)
    && IF (
        'Vendor List'[EU / Non-EU] = "non-EU (USD)",
        'Stock Forecast'[Total Direct Cost (Excl. Last Unit Cost) (Excl. VAT)] * 1,
        35
            / CALCULATE ( 'Currency Rate'[EUR per unit], 'Currency Rate'[Currency] = "USD" )
            && IF (
                'Vendor List'[EU / Non-EU] = "non-EU (USD)",
                'Stock Forecast'[Total Direct Cost (Excl. Last Unit Cost) (Excl. VAT)] * 1,
                03
            )
    )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Pragati11]

Hi @Niels_T ,

If the tables that you are using in the DAX expression have a relationship in Power BI, then use RELATED function on one of the tables in the DAX expression.

[Niels_T]

Instead of making a measure, I tried making a column. This almost fixes my problem.

I then tried to use related on Vendor List but that didn't work for me.

There is a relationship between the two tables.

[Pragati11]

Hi @Niels_T ,

I honestly thought it was a column as I don't see any summarisation on the table columns.

[Niels_T]

I changed it now to column. 

As you can see some of the columns are still greyed out (see screenshot). Is there a special operator that I need to use to select these?

[Pragati11]

Hi @Niels_T ,

Just looking at your DAX, I see folowing issues::

• On Line 4 you are missing a closing bracket for DIVIDE function
• You are missing an ELSE condition for your last IF statement

Also try to check your DAX formula on DAX formatter as it gives a good indight on brackets missing, etc. https://www.daxformatter.com/

Also why ou are not using RELATED dax function for vendor table?

Also try to copy and paste your DAX here rather than sharing a screenshot.