达克斯过滤表达式，每天的最后日期 [英] 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个新的挡泥板单元格3"显示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]) - 为每个商店查找其最新的(输入" [日期]，输入" [时间])对(
GENERATE的第二个arg) - 使用(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.
这是一个交叉表(Matrix可视化表)，在行上具有日期和时间，在列上具有商店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.在15:25，商店600:109也有一个记录.我们可以在时间级别上查看这些单独的交易.在日期小计中，我们看到商店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，从9/5在09:43开始，它是49.对于商店600，这是9:3在15:25时的109.总计(总行，总计列)是这两个值-每个商店的最新值-加在一起: 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 ，以在(存储，日期，时间)获得最小的金额)元组.
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.
这篇关于达克斯过滤表达式，每天的最后日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
