在表格模型中左连接 [英] Left join in tabular model

问题描述

我有2张桌子，如下所示.

I have 2 tables as below.

BILLING
AccountId   RatingDate                  Load
1280582     2018-01-04 15:20:13.000     130.000
421947      2018-01-04 11:44:58.000     176.000
1033717     2018-01-04 10:52:23.000     234.000

RATING
AccountId   RatingDate                  RatingMethod
1280582     2018-01-04 15:20:13.000     A
421947      2018-01-04 11:44:58.000     A
1033717     2018-01-04 10:52:23.000     A

我需要在计费"表中创建一个度量以计算负荷"列的总和.我可以执行相同的SQL查询，但是无法使用相同的逻辑创建度量.

I need to create a measure in Billing table to calculate the sum of Load column. I have the SQL query to perform the same but I am not able to create the measure with the same logic.

SELECT SUM(b.Load) 
FROM Billing b
LEFT JOIN Rating r
ON b.AccountId = r.AccountId
AND b.RatingDate = r.RatingDate                               
WHERE r.RatingMethod = 'A' 

有人可以用dax公式帮助我创建度量吗?

Can someone help me with the dax formula for creating the measure?

推荐答案

有几种解决方法，包括

1. 在查询编辑器中进行左联接.

1. Do the left join in the query editor.

创建桥表或唯一索引列以关联两个表.

Create bridge tables or unique indexing columns to relate your two tables.

使用LOOKUPVALUE函数编写度量.

如果您要进行其他操作，则可能需要考虑选项#1或#2，但是仅针对此特定措施，我将向您展示#3.

If you're doing a bunch of other stuff, you'll probably want to consider option #1 or #2, but for just this particular measure, I'll show you #3.

LoadSum = CALCULATE(SUM(Billing[Load]),
                    FILTER(Billing,
                           LOOKUPVALUE(Rating[RatingMethod],
                                       Rating[AccountID], Billing[AccountID],
                                       Rating[RatingDate], Billing[RatingDate])
                           = "A"))

在过滤Rating表中的[RatingMethod] = "A"行(并在ID和日期列上匹配)之后，以上内容对[Load]求和.

The above sums over the [Load] after filtering rows where [RatingMethod] = "A" in the Rating table (and matching on the ID and date columns).

对于#1，选择您的Rating查询，然后单击合并查询(主页"选项卡).

For #1, select your Rating query and click Merge Queries (Home tab).

选择要连接的列(按住Ctrl键并单击它们的顺序相同).保留默认的左外部联接"，然后单击确定".

Select the columns you want to join one (Ctrl+Click in the same order on both of them). Keep the default Left Outer Join and hit OK.

一旦合并，您将拥有一个名为Billing的新列.单击列右上角的展开图标，然后选择要扩展的列(仅选择Load).

Once they're merged, you'll have a new column named Billing. Click the expand icon in the top right corner of the column and select which columns you want to expand (select Load only).

展开后，您的表应如下所示:

Once expanded, your table should look like this:

RATING
AccountId   RatingDate            RatingMethod  Load
1280582     2018-01-04 15:20:13   A             130
421947      2018-01-04 11:44:58   A             176
1033717     2018-01-04 10:52:23   A             234

这篇关于在表格模型中左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！