0
I need to do left joins in my data model in Power BI. I import my model from SQL Server where I have one big fact table with multiple relationships to dimension tables. Power BI only allows me to do inner joins which allows me to put to my report only one type of fact, when I want to put other type of fact, I would need left join? Someone having same issues or any experiences how to do left joins in Power BI?
DSCP
Posted 2019-06-30T19:29:49.207
Reputation: 1
0
It seems from your comments your data is modeled in a Attribute-value pair style. I would create 3 filtered Queries in the Power BI model to split the Fact table data by Category.
With those 3 tables in place in the model, I would create relationships from each Fact-based table to the Contract table. Then Power BI will naturally return all the Interest rates, Residual values and Prices data for each Contract.
Mike Honey
Posted 2019-06-30T19:29:49.207
Reputation: 2 119
Yes this would solve the problem, the thing is that that my fact table has about 150 000 000 rows and 100 different Categories involved. I could write those queries once, but would it be possible to somehow have it stored and always when I want to create report and I load my Fact table from SQL server this queries will automatically filter it to 100 small tables? I need to deploy these solution to other users as well. Thxvm – DSCP – 2019-07-07T13:20:17.817
By Queries I was referring to creating Query objects in the Power Query Editor. Custom SQL shouldn't be needed. So yes, the Query definitions would be stored in the Power BI file. – Mike Honey – 2019-07-07T23:11:14.027
Be more specific and add an example. – Mike Honey – 2019-07-02T12:54:23.710
Simple example: I have a Fact table which stores Interest rates, Residual values and Prices on particular business contracts. The fact table itself consist of FkContractId - FK to table Contract, FKCategory - FK to table Category and Value - the value of fact itself. In Category table I have 3 items - Interest Rates = 1, Residual Values = 2 and Price = 3. So FkCategory in Fact table has values from 1 to 3 based on what kind of Category it refers to. I import my simple model to Power BI and want to make report with all types of facts. But I can only join Fact table with Inner Join… Any idea?Thx – DSCP – 2019-07-03T15:53:34.317
I'm not following the issue - you have Category values 1-3 in both Fact and Dimension tables, so how is a "left join" relevant? – Mike Honey – 2019-07-03T20:28:59.690
Imagine I want report with all 3 kinds of value - Interest rates, Residual values and Prices. Once I join Fact table to Contract table and I specify Category of fact = FkCategory = 1, I can display Interest rates. But to display also residual values, I need to join Fact table again, this time with FkCategory = 2..And this is not possible without Left join as when I joined Fact table with FkCategory=1 I had to do INNER JOIN, which limits my final report just to the Facts that have FkCategory = 1. I hope it is clear. – DSCP – 2019-07-05T08:51:39.620