Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jessewysong
Helper III
Helper III

Linking on premises SQL tables in a manner similar to Crystal Reports

Hello,

A newbie question... We have an on premises SQL database which we usually report on using Crystal Reports. I noticed Power BI was included with our 365 subscription and had some great features. One thing I am stumbling on is how to link views in a manner I am used to. Am I comparing apples to apples in the screen shots below? I notice the terms are different. I am used to linking key fields that exist on both views so I can then integrate data from both views into a single report. I've tried a few things in Power BI but they usually spin out or freeze with a process lock or other error im not familier with. I have created Power BI reports with the greatest of ease if I get data from an excel doc but am going down in flames trying to master a a simple SQL one. 

 

Thanks!

 

 

 

 linking in crystal.JPGlinking in powerbi.JPG

4 REPLIES 4
Greg_Deckler
Super User
Super User

@jessewysong - So, it looks like you are trying to do some joins between tables/views. There are lots of ways to do that.

 

1. When connecting to the SQL Server, you could just enter the SQL code that does the joins, the optional area "SQL statement (option)"

 

2. In Power Query M code, you can create separate queries to your SQL tables and then use Merge queries to control the joins between them.

 

I have a blog article on Merge queries in M that could help get you started.

Merge Queries with M

http://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx

 

You will then probably want to read about Table.NestedJoin:

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

 

You can do Left Outer, Inner, Right Outer and Full Outer joins.

 

3. You can use the relationships in the Relationships area of Desktop but that is really not its purpose to do a join in the manner that you are probably thinking about. It can definitely work to have a similar effect when combined with visualizations and filters but it is not really doing what you probably want it to do. That being said, DAX does have some "Other" functions that do joins, unions, etc. that probably would act more like you expect. https://msdn.microsoft.com/en-us/library/mt150101.aspx

 

All of this being said, the real question comes down to what you are specifically trying to accomplish.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
jessewysong
Helper III
Helper III

An example of data I am trying to get... Imagine I have an "Invoice Detail" view linked to "Product" view (using a key field called ProdutID which existis in both views) and would like to see a summary graph of sales by product category. The sales data exists in Invoice Detail and the product category in the Product view. In Crystal Reports I could get the data to show up in a 10 seconds or so by adding a summary to a group footer in a report. Attemtping to add both those fields to a Power BI visual results in the report spinning away for eternity even if I limit my data to the last 6 months or something (our data actually goes back 6 years). 

 

 

 

 

@jessewysong - Posted my response before seeing your second posting. This seems like a pretty straight-foward thing, so you have something like (slimplified):

 

Invoice Detail

ProductID,Value

 

Product

ProductID,Category

 

You want to put Category and Value into a visualization with "Category" on your Axis and "Value" as your Value in the, let's say column chart visulization.

 

As long as each ProductID exists in your Product table is unique, all you should have to do is drag ProductID from Invoice Detail to ProductID in Product table in relationships area and then do what I indicate above.

 

How much data are we talking about? How many Categories and how many product ID's?

 

Another way to do this would might be to create a "Categories" table with only the categories listed once, link Products table to Categories table via Category Custom Column in your Categories table with the formula:

Total = SUMX(RELATEDTABLE('Invoice Detail'),'Invoice Detail'[Value])

Be interesting to see if transitive relationships allowed that to work.

 

You could then just put Category and Total in the chart visualization.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hello,

I appreciate the reply. Couple of thoughts:

 

We are talking 8 product categories and perhaps 3,000 product ID's.

 

I'm going to review the Power Query M mode info as the way I would successfully link such data in Crystal is by using a "left outer join" and an "enforce join" that is set to "not enforced", and a "link type" that is set to "=". You mentioned choosing a left outer join which seems like a lead.

 

The nested tables seems promising too, I just don't know any M or have any basic query skills unfortunatley. This one is no short I might be able to figure it out though. 

 

I did get my simple visual to work finally, but now need to go back and see what I did. I got distracted early in the week and abandoned it mid stream. Last wrinkle to throw out there is we are awaitng a new on premises server so we dont have that Power BI Enterpirse gateway installed... I wonder if getting that going would help on reports where I am looking at way too much data. 

 

Thanks again for the replies.

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors