How to join tables on multiple columns in Power BI Desktop

Question

The goal is to create a visual that shows Budget vs Premium for each month in a year; Something like that:

So I have two tables BudgetData and Premiums. How can I join those two table on two columns:

    `BudgetData.InsurenceType = Premiums.Division 
and
 BudgetData .MonthYear = Premiums.MonthYear`

BudgetDate Table:

   InsurType         Amount     MonthYear
Commercial Auto      1000       Jan 2017
Commercial Auto      22000      Feb 2017
Commercial Auto      3000       Mar 2017
Specialty Casualty   4000       Jan 2017
Specialty Casualty   5000       Feb 2017
Specialty Casualty   35000      Mar 2017

Premiums Table:

Division          Premium    MonthYear
Commercial Auto      2500     Jan 2017
Commercial Auto      1800     Feb 2017
Commercial Auto      3365     Mar 2017
Specialty Casualty   14528    Jan 2017
Specialty Casualty   3323     Feb 2017
Specialty Casualty   1825     Mar 2017

In Power BI I can only join on single relationship, which is I dont understand why its made that way.

So what would be the most efficient way to achieve that? In terms of performance.

UPDATE:

Joe, thank you very much. I was able to join. But when I try to do the visual it gives me Budget Amount either count or sum

I set Don't summarize and made sure datatypes are the same with Premium value (Decimal Number) but still having this issue.

Is it something to do with join?

UPDATE2:

.pibx file is avalable via dropbox: https://www.dropbox.com/s/mcj1gtonttjtz6y/PremiumByDivisions.pbix?dl=0

UPDATE 3

Joe, what if I create separate calculated table from existing tables Premiums and BudgetData with columns: Premium, BudgetAmount, Division and MonthYear. Somehting like that:

Then use it in my chart. Do you think it will filter by Division if I click on Donut chart?

The problem is I know how to create such table in SQL, but dont know how to do that in DAX. Below is t-sql code:

;with cte_Premiums
as
(
select  sum(Premium) as Premium, 
        Division, 
        LEFT(DATENAME(MONTH,[EffectiveDate]),3) +' '+ CONVERT(varchar(4),Year(EffectiveDate)) AS MonthYear,
        MAX(DATEPART(MM, [EffectiveDate])) as DateOrder
from ##OlegTest 
group by LEFT(DATENAME(MONTH,[EffectiveDate]),3) +' '+ CONVERT(varchar(4),Year(EffectiveDate)), Division
)

,cte_Budget
AS
(
select  insurType,
        Amount, 
        LEFT(DATENAME(MONTH,[PostDate]),3) +' '+CONVERT(varchar(4),Year([PostDate])) AS MonthYear
from BudgetData
)
select  Premium, 
        Amount as BudgetAmount, 
        Division, 
        p.MonthYear
FROM    cte_Premiums p INNER JOIN cte_Budget b ON p.Division = b.insurType and p.MonthYear = b.MonthYear
ORDER BY Division,DateOrder

Answer 1

Short answer is that Power BI doesn't support joining on two columns... but I don't think that is the problem you need to solve here.

Without knowing your full data model, I'm going to make some guesses, but I hope this clears things up for you.

I started with a BudgetData table like this:

and a Premiums table like this:

I used Power BI to create a date table with the following DAX formula.

Dates = CALENDAR(DATE(2017, 1, 1), DATE(2017, 12, 31))

I also used Power BI to create a dimension table for InsurType/Division with the following DAX formula.

InsurTypes = DISTINCT(
    UNION(
        SELECTCOLUMNS(BudgetData, "InsurType", BudgetData[InsurType]),
        SELECTCOLUMNS(Premiums, "InsurType", Premiums[Division])
    )
)

Then I created relationships between the four tables as shown below.

Since you are using a MonthInCalendar column that I don't have, I used the following formulas to create it and a sorting column.

MonthInCalendar = LEFT(Dates[Date].[Month], 3) & " " & Dates[Date].[Year]

MonthInCalendarSort = Dates[Date].[Year] & Dates[Date].[MonthNo]

Then I created a graph configured as shown below, which appears to display the correct information. No relationship between the BudgetData and Premiums tables needed.

Answer 2

You CAN select multiple columns for a merge.

Start by loading both tables (BudgetDate & Premiums) into Power Query.

Then click Home -> Merge Queries -> Merge Queries as New:

Then, in the top section of the Merge window, select 'Premiums' (in the dropdown selector), then click on the 'Division' column header, then press and hold your Ctrl button and click on the 'MonthYear' column header. (You'll see a '1' and a '2' appear beside each column header...make sure they appear in the proper order, i.e., 'Division 1' and 'MonthYear 2').

Then, in the next section of the Merge window, select BudgetDate (in the dropdown selector), then click on the 'InsurType' column header, then press and hold your Ctrl button and click on the 'MonthYear' column header. (You'll see a '1' and a '2' appear beside each column header...make sure they appear in the proper order, i.e., 'InsurType 1' and 'MonthYear 2').

Your Merge window like this:

And when you click OK, you'll get this:

And when you click on this... in the header of 'NewColumn'... you'll get this:

Clear the check mark beside 'Use original column name as prefix' and click OK to get this:

Answer 3

The create relationship dialog doesn’t allow to select multiple columns So there are 2 workarounds 1. Create a new column on both tables with concatenation InsurType & MonthYear

2. Power Query can join tables with as many as columns you want.First open Merge Queries from the Combine section of Home tab,You can hold CTRL key and select columns one by one (in the right order of joining).