Dax过滤表达式，每天的最后一个日期 [英] Dax filtering expressions, last date of every day
问题描述
我正在尝试从包含库存信息的全局表中绘制数据.
I am trying to graph data from a global table that contains information about inventory.
该表的架构为(库存数量、库存销售、日期、时间、商店)
The table has a schema of (inventory amount, inventory sales, date, time, store)
挑战是:每家商店每天记录 4-5 行关于库存和销售的信息，而我有 4 家商店.
Challenge is: every store logs 4-5 rows every day about inventory and sales and I have 4 stores.
我的目标是将此表过滤成一个新表，其中仅包含每个商店的最后库存量和每天的库存销售量.
My goal is to filter this table into a new table that includes only the last inventory amount and inventory sales of every day for every store.
下面是我的输入表:
我想生成如下所示的输出.请注意，仅保留 9 月 2 日商店 600 的第二个条目.
I'd like to generate an output like below. Note that only the second entry for store 600 on 9/2 is kept.
欢迎提出任何建议.
谢谢.
我尝试了一些过滤表达式，但没有一个对我有用.在此处输入图片说明
I have tried some filtering expressions but none worked worked for me. enter image description here
[更新]:所以我从下面的答案中尝试了这个例子，我得到了这个:
[Update]: So I tried the example from the answer below and I got this:
2019 年 9 月 4 日，星期三，单元格:1 新的 sill-fender cell3"显示 18023；它应该只显示 1386
"Wednesday september 4th 2019, cell: 1new sill-fender cell3" is showing 18023; it should only show 1386
推荐答案
通常我会建议在 M 中执行此操作.它有点冗长，但我认为发生的事情要清楚得多.我在 M 和下面的 DAX 中有一个解决方案.对于任何一种解决方案，方法都完全相同:
Typically I would suggest doing this in M. It's a bit more verbose, but I think much clearer in what's happening. I've got a solution in M and in DAX below. For either solution, the approach is exactly the same:
- 构建 (Store, Date, MaxTime) 的辅助结构，该结构由唯一的 (Store, Date) 对组成，并具有该对存在的最新时间.
- 按此帮助表中的值过滤输入表.
先M解:
// Input - query with raw data - no work here:
let
Source = #table(
{"inventory amount", "Sale in dollar", "date", "time", "store"},
{
{54, 100, "2019-09-03", "09:55:00", 500},
{52, 200, "2019-09-04", "10:34:00", 500},
{49, 300, "2019-09-05", "09:43:00", 500},
{112, 200, "2019-09-02", "13:13:00", 600},
{111, 100, "2019-09-02", "13:19:00", 600},
{109, 200, "2019-09-03", "15:25:00", 600}
}
),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"inventory amount", Int64.Type}, {"Sale in dollar", Int64.Type}, {"date", type date}, {"time", type time}, {"store", Int64.Type}})
in
#"Changed Type"
// StoreDateMaxTime - this is our helper table. This is set not to load into the
// model, just used in PQ for data processing.
let
Source = Input,
#"Grouped Rows" = Table.Group(Source, {"date", "store"}, {{"MaxTime", each List.Max([time]), type time}})
in
#"Grouped Rows"
// Output - our final table
let
Source = Input,
#"Merged Queries" =
Table.NestedJoin(
Source, {"store", "date", "time"},
StoreDateMaxTime, {"store", "date", "MaxTime"},
"HelperDedupe",
JoinKind.Inner),
// Note that we join above, but don't use the results of the join in any way
// we only care about the filtering semantic of an inner join.
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"HelperDedupe"})
in
#"Removed Columns"
这是 DAX 版本做的完全相同的事情:
Here is the DAX version doing exactly the same thing:
Output2 =
VAR StoreDateMaxTime =
ADDCOLUMNS (
GROUPBY (
'Input',
'Input'[store],
'Input'[date]
),
"MaxTime", CALCULATE ( MAX ( 'Input'[time] ) )
)
RETURN
CALCULATETABLE (
'Input',
TREATAS ( StoreDateMaxTime, 'Input'[store], 'Input'[date], 'Input'[time] )
)
请注意，您也可以只加载完整的输入表并定义一个度量，如下所示，它将返回最后一个库存记录:
Note that you could also just load the full input table and define a measure as follows that will return the last inventory record:
FinalInventoryAmount =
VAR LastStoreDateTimes =
GENERATE(
VALUES ( 'Input'[store] ),
CALCULATETABLE (
TOPN (
1,
GROUPBY (
'Input',
'Input'[date],
'Input'[time]
),
'Input'[date], DESC,
'Input'[time], DESC
)
)
)
RETURN
CALCULATE (
SUM ( 'Input'[inventory amount] ),
TREATAS ( LastStoreDateTimes, 'Input'[store], 'Input'[date], 'Input'[time] )
)
这个措施看起来应该与制作表格的模式非常相似.我们再次进行了类似的操作.但是为了让它与过滤和总计一起工作，我们需要做一些稍微不同的事情:
This measure should look fairly similar to the pattern for making the table. We're again doing a largely similar operation. But to make it work well with filtering and totals, we need to do something slightly different:
- 仅从上下文中的商店开始:
VALUES ( 'Input'[Store] ) - 为每个商店查找其最新的 ('Input'[Date], 'Input'[Time]) 对(
GENERATE的第二个参数) - 使用 (2) 中的那些值来过滤事实表，并且只考虑每个存储的最新行(`CALCULATE).
您会看到，在总计级别返回 158，这是商店 500 的最后一个值(9/5 的 49)和商店 600 的最后一个值(9/3 的 109)得到 49 + 109 = 158.
You'll see that at the grand total level, this is returning 158, which is the last value for store 500 (the 49 from 9/5) and the last value for store 600 (the 109 from 9/3) to get 49 + 109 = 158.
这是使用度量的视觉效果:
Here's a visual using the measure:
详细解释该措施的工作原理.
explaining more about how the measure works.
这是一个交叉表(矩阵视觉对象)，行上显示日期和时间，列上显示商店 ID.
Here is a cross-tab (a Matrix visual), with date and time on the rows, and the store id on the columns.
让我们来看看这个.在 9 月 2 日，商店 500 没有记录.商店 600 有两条记录.我们可以看到它们各自的时间.后者是 13:19 的 111.可以看到，9月2日600店的总数是后来的111.9月2日，所有门店的总数是111，就是600店的最新金额.
Let's walk through this. On 9/2, there are no records for store 500. There are two records for store 600. We can see the individual times of those. The later is 111 at 13:19. You can see that the total for store 600 on 9/2 is the later amount, 111. The total for 9/2, across all stores is 111, the latest amount for store 600.
在 9 月 3 日，商店 500:54 在 09:55 有一条记录.商店 600:109 在 15:25 也有一条记录.我们可以在时间级别看到这些单独的交易.在日期小计中，我们看到商店 500 和 600 的金额彼此相邻.9/3 的总计列显示了添加的 54 + 109 = 163，这是每个商店的最新数量相加，是所有商店的总和.
On 9/3, there is one record for store 500: 54 at 09:55. There is also a single record for store 600: 109 at 15:25. We can see these individual transactions at the time level. At the date subtotal, we see the amount for store 500 and 600 next to one another. The total column for 9/3 shows the addition of 54 + 109 = 163, which is the latest amount for each store added together for a total across all stores.
9/4 和 9/5 很无聊，每个商店 500 都只有一次交易.
9/4 and 9/5 are boring, each with only a single transaction for store 500.
总计行显示每个商店的值和总计列中的总计.商店的总数是该商店的最后一个值.对于商店 500，这是 49，从 9 月 5 日 09:43 开始.对于商店 600，这是 109，从 9 月 3 日 15:25 开始.总计(总行、总列)是这两个值 - 每个商店的最新值 - 相加:49 + 109 = 158.
The Total row shows values for each store and a grand total in the total column.
The total for a store is the last value for that store. For store 500, this is 49, from 9/5 at 09:43. For store 600, this is 109, from 9/3 at 15:25. The grand total (total row, total column) is these two values - the latest from each store - added together: 49 + 109 = 158.
总计仅考虑每个商店的一个值 - 无论上下文中的最新值.
The totals only consider one value from each store - whatever the latest one is in context.
Edit2: 适用于同时具有多条记录的值.仅更新度量，但如何为 M 和 DAX 表版本包含相同的值应该很明显.
for values that have multiple records at the same instant. Updating the measure only, but it should be obvious how to include the same value for the M and DAX table versions.
FinalInventoryAmount v2
VAR LastStoreDateTimes =
GENERATE(
VALUES ( 'Input'[store] ),
CALCULATETABLE (
SAMPLE ( // SAMPLE will ignore ties for those identical rows
1,
GROUPBY (
'Input',
'Input'[date],
'Input'[time],
'Input'[inventory amount]
),
'Input'[date], DESC,
'Input'[time], DESC,
'Input'[inventory amount], ASC // make sure we're getting the min
// amount value.
)
)
)
RETURN
CALCULATE (
SUM ( 'Input'[inventory amount] ),
// and below is the other difference
TREATAS (
LastStoreDateTimes,
'Input'[store],
'Input'[date],
'Input'[time],
'Input'[inventory amount] // we're also only including rows that tie
// on the min amount here.
)
)
如果您不关心总计行为，您也可以将 SUM 替换为 MIN 以在 (store, date, time) 元组.
If you don't care about totaling behavior, you could also just replace the SUM with a MIN to get the smallest amount at a (store, date, time) tuple.
这篇关于Dax过滤表达式，每天的最后一个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
