如何在Power BI Desktop中联接多列上的表

本文介绍了如何在Power BI Desktop中联接多列上的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

目标是创建一个显示一年中每个月的预算vs溢价的图像； 这样的东西：

因此，我有两个表BudgetData和Premiums。 如何在两列上连接这两个表：

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

BudgetDate表：

保险类型月份年份商用汽车1000年1月2017年商用汽车22000年2月2017年bb $ b商用汽车3000年3月特殊伤亡人数4000 2017年1月特殊伤亡人数5000 2月2017 特殊伤亡人数35000 2017年3月

Premiums Table：

Division Premium Month Year Commercial Auto 2500 2017年1月 Commercial汽车1800 2017年2月商用汽车3365 2017年3月专业伤亡14528 2017年1月专业伤亡3323 2017年2月专业伤亡1825年3月18b

在Power BI中，我只能加入单一关系，这是我不明白为什么这样做的原因。

那么实现这一目标的最有效方法是什么？在性能方面。

更新：

Joe，非常感谢。 我能够加入。 但是当我尝试进行视觉处理时，它会为我提供预算金额 count 或 sum

我设置了不要汇总，并确保数据类型与Premium值（十进制数）相同，但仍然存在此问题。

与连接有关吗？

UPDATE2：

.pibx文件可通过保管箱获取：

然后在我的图表中使用它。如果我单击甜甜圈图，您是否认为它会按司进行过滤？

问题是我知道如何在SQL中创建此类表，但不知道该怎么做在DAX中。 以下是t-sql代码：

;其中cte_Premiums 作为（ select sum（Premium）as Premium， Division， LEFT（DATENAME（MONTH，[EffectiveDate]），3）+''+ CONVERT（varchar（4），Year（EffectiveDate））AS月， MAX（DATEPART（MM，[EffectiveDate]））作为DateOrder 从## OlegTest 组，按LEFT（DATENAME（MONTH，[EffectiveDate]），3）+''+ CONVERT（varchar（4），Year（EffectiveDate）），部门） ，cte_Budget AS （ select insurType，金额，，左边为LEFT（DATENAME（MONTH，[PostDate]），3）+''+ CONVERT（varchar（4），Year（[PostDate]））从预算数据中以月份表示选择高级，作为预算金额，部门， p。每月年份来自cte_Premiums p内联cte_Budget b ON p.Division = b.insurType和p。 MonthYear = b.MonthYear ORDER BY Division，DateOrder

解决方案

短答案可能是Power BI不支持两列的连接...但是我不认为这是您需要在此处解决的问题。

不知道您的完整的数据模型，我会做出一些猜测，但我希望这可以为您解决问题。

我从这样的BudgetData表开始：

和这样的Premiums表：

我使用Power BI创建具有以下DAX公式的日期表。

日期=日历（DATE（2017，1，1），DATE（2017，12，31））

然后，我在四个表之间创建了关系，如下所示。

由于您使用的是 MonthInCalendar 列，有，我使用以下公式创建了它并创建了一个排序列。

MonthInCalendar = LEFT（Dates [Date] 。[月]，3）& & Dates [Date]。[Year] MonthInCalendarSort = Dates [Date]。[Year]& Dates [Date]。[MonthNo]

然后我创建了一个如下所示配置的图形，显示正确的信息。

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: www.dropbox/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

解决方案

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.