Lookup values (in DirectQuery)

[Ged]

Hi All,

I searched the forum for this case, but I was not able to find the right solution. Hopefully, you could help me out. 

Situation: 

I'm connected to an SQL server dbo (not an SSAS solution) via DirectQuery. 

I have multiple dimension tables and a single fact table. 

Fact table view (took it from SQL):

Account table view (took it from SQL)::

I  would like to create 2 columns (i.e. YTD 2 & Periodic 2) in the Fact table where I  multiply the values in the YTD and Periodic columns in the Fact table by the respective SUMSIGN in the Account table. 

The reason behind is that I need to create Gross Profit, Gross Operating Profit figures. 

E.g. Gross Profit (SUMACCOUNT), it is the SUM of Revenue (sum of internal and external revenue) and the SUM of Cost of Sales (sum of internal and external COS):

However, when I use COS in the Report View it appears as a positive figure and therefore, my Gross Profit becomes = SUM(Revenue) + positive SUM(COS) and shows me an incorrect figure. I want to make an enforced transformation here.

Existing relationship is:

I already tried lookup and related DAX but they do not seem to work in the DirectQuery mode? 

Any changes to the existing database structure in the backend - out of question.

I'm required to perform the aforesaid in the Power BI environment. 

I would appreciate your help...

Thank you

Ged

[mahenkj2]

@Ged, @Bone 

Related works in a DAX calculated column or a in a measure. You may try there it again.

[Ged]

Just an update: perhaps my description was too wordy, but in a nutshell I need to lookup values from my Account table into my Fact table (Fact table being the many side of the many-to-one relationship).

Lookupvalue DAX does not work as I'm working in the DirectQuery mode. All the posts I searched for had cases where people were working in the Import mode.

[v-caliao-msft]

@Ged,

You could use RELATED function to get a related value from another table. 

Reference

https://msdn.microsoft.com/en-us/library/ee634202.aspx

Regards,

Charlie Liao

[Ged]

@v-caliao-msft

Thank you Charlie. Appreciate it.

I had tried RELATED before by trying to add a Custom Column in the Query Editor. However, I received this error:

Expression.Error: The name 'RELATED' wasn't recognized.  Make sure it's spelled correctly.

Expression was:

= Table.AddColumn(#"Added Custom", "Custom", each RELATED(Account_Code_ACCF[SUMSIGN]))

I tried adding apostrophes around the table name. No breakthrough though. I am new in Power BI.

Would you please tell me the difference between adding a New Column in the Report view and adding a Custom Column in the Query Editor?

I sorted my question by creating and using an inner join statement to import a new table via Direct Query.

Is that an accepted practise? I'm thinking whether I should expect any issues , e.g. re performance.

Thank you again,

Ged

[Bone]

Did you ever figure this out?  I am in the same boat.  I have a lookup table import and a direct query but the lookup won't work.