cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ged Regular Visitor
Regular Visitor

Lookup values (in DirectQuery)

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):

Fact Table.JPG

 

Account table view (took it from SQL)::

Account Table.JPG

 

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):

 

GrossProfit.JPG

 

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:

Fact_Account relationship.JPG

 

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

3 REPLIES 3
Ged Regular Visitor
Regular Visitor

Re: Lookup values (in DirectQuery)

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.

Moderator v-caliao-msft
Moderator

Re: Lookup values (in DirectQuery)

@Ged,

 

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

Reference

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

 

Capture.PNG

 

Regards,

Charlie Liao

Ged Regular Visitor
Regular Visitor

Re: Lookup values (in DirectQuery)

@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