如何在 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 2017 年 3 月专业伤亡 4000 2017 年 1 月专业伤亡 5000 2017 年 2 月专业伤亡 35000 2017 年 3 月
保费表:
Division Premium MonthYear商用车 2500 2017 年 1 月商用汽车 1800 2017 年 2 月商用汽车 3365 2017 年 3 月专业伤亡 14528 2017 年 1 月专业伤亡 3323 2017 年 2 月专业伤亡 1825 2017 年 3 月
在 Power BI 中,我只能加入单一关系,这就是我不明白为什么会这样.
那么实现这一目标的最有效方法是什么?在性能方面.
更新:
乔,非常感谢.我能够加入.但是当我尝试做视觉时,它会给我预算金额 count
或 sum
我设置了 Don't summary
并确保数据类型与 Premium 值(十进制数)相同,但仍然存在此问题.
跟join有关系吗?
更新2:
.pibx 文件可通过 dropbox 获取:
然后在我的图表中使用它.如果我点击圆环图,你认为它会按部门过滤吗?
问题是我知道如何在 SQL 中创建这样的表,但不知道如何在 DAX 中创建.下面是t-sql代码:
;使用 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)) 分组,除法),cte_Budget作为(选择保险类型,数量,LEFT(DATENAME(MONTH,[PostDate]),3) +' '+CONVERT(varchar(4),Year([PostDate])) AS MonthYear来自预算数据)选择高级,金额为 BudgetAmount,分配,p.月年FROM cte_Premiums p INNER JOIN cte_Budget b ON p.Division = b.insurType 和 p.MonthYear = b.MonthYearORDER BY Division,DateOrder
简而言之,Power BI 不支持加入两列...但我认为这不是您需要在这里解决的问题.
在不了解您的完整数据模型的情况下,我会做出一些猜测,但我希望这能为您解决问题.
我从一个这样的 BudgetData 表开始:
还有这样的保费表:
我使用 Power BI 创建了一个包含以下 DAX 公式的日期表.
日期 = CALENDAR(DATE(2017, 1, 1), DATE(2017, 12, 31))
我还使用 Power BI 使用以下 DAX 公式为 InsurType/Division 创建了维度表.
InsurTypes = DISTINCT(联盟(SELECTCOLUMNS(BudgetData, "InsurType", BudgetData[InsurType]),SELECTCOLUMNS(保费,InsurType",保费[部门])))
然后我创建了四个表之间的关系,如下所示.
由于您使用的是我没有的 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屋!