Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
@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.
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.
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.