cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ged
Helper I
Helper I

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
Helper I
Helper I

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.

@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

@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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors