如何在 Power BI Desktop 中连接多列上的表 [英] How to join tables on multiple columns in Power BI Desktop

问题描述

目标是创建一个视觉效果，显示一年中每个月的预算与溢价；类似的东西:

所以我有两个表 BudgetData 和 Premiums.如何在两列上加入这两个表:

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

预算日期表:

 InsurType Amount MonthYear商用车 1000 强 2017 年 1 月商用车 22000 2017 年 2 月商用车 3000 Mar 2017专业伤亡 4000 2017 年 1 月专业伤亡 5000 2017 年 2 月特殊伤亡 35000 2017 年 3 月

保费表:

Division Premium MonthYear商用车 2500 2017 年 1 月商用车 1800 2017 年 2 月商用汽车 3365 Mar 2017专业伤亡 14528 2017 年 1 月特殊伤亡 3323 2017 年 2 月特殊伤亡 1825 年 3 月 2017

在 Power BI 中，我只能加入单一关系，这是我不明白为什么会这样.

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

更新:

乔，非常感谢.我能够加入.但是当我尝试做视觉效果时，它给了我预算金额 count 或 sum

我设置了 Don't summary 并确保数据类型与 Premium 值(十进制数)相同，但仍然存在此问题.

是否与加入有关?

更新2:

.pibx 文件可通过 dropbox 获取:

然后在我的图表中使用它.如果我点击甜甜圈图表，你认为它会按部门过滤吗?

问题是我知道如何在 SQL 中创建这样的表，但不知道如何在 DAX 中创建.下面是t-sql代码:

;with cte_Premiums作为(选择 sum(Premium) 作为 Premium，分配，LEFT(DATENAME(MONTH,[EffectiveDate]),3) +' '+ CONVERT(varchar(4),Year(EffectiveDate)) AS MonthYear,MAX(DATEPART(MM, [EffectiveDate])) 作为 DateOrder来自##OlegTest按 LEFT(DATENAME(MONTH,[EffectiveDate]),3) +' '+ CONVERT(varchar(4),Year(EffectiveDate)), Division 分组),cte_Budget作为(选择保险类型，数量，LEFT(DATENAME(MONTH,[PostDate]),3) +' '+CONVERT(varchar(4),Year([PostDate])) AS MonthYear来自预算数据)选择高级，金额作为预算金额，分配，p.MonthYearFROM cte_Premiums p INNER JOIN cte_Budget b ON p.Division = b.insurType 和 p.MonthYear = b.MonthYearORDER BY Division,DateOrder

解决方案

简而言之，Power BI 不支持加入两列……但我认为这不是您需要在这里解决的问题.

在不知道您的完整数据模型的情况下，我将进行一些猜测，但我希望这可以为您解决问题.

我从一个这样的 BudgetData 表开始:

还有一个像这样的 Premiums 表:

我使用 Power BI 创建了一个包含以下 DAX 公式的日期表.

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

我还使用 Power BI 使用以下 DAX 公式为 InsurType/Division 创建维度表.

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

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

由于您使用的是我没有的 MonthInCalendar 列，因此我使用以下公式来创建它和一个排序列.

MonthInCalendar = LEFT(Dates[Date].[Month], 3) &" " &日期[日期].[年]MonthInCalendarSort = Dates[Date].[Year] &日期[日期].[月号]

然后我创建了一个如下图配置的图形，它似乎显示了正确的信息.不需要 BudgetData 和 Premiums 表之间的关系.

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

解决方案

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.

这篇关于如何在 Power BI Desktop 中连接多列上的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！