表关系以确保当表中的日期重叠时使用日期切片器过滤表 [英] Table relationship to ensure table filters with the date slicer when the dates in table overlap

问题描述

我有一张桌子人员预订，其中包含开始日期和结束日期的工作。我也有一个计算表，该表已提交

I have a table Staff Booking containing Start date and End Date for a job. I also have a calculated table filed with

Date Range 2 = CALENDAR(MIN('Staff Booking'[Start Date]), MAX('Staff Booking'[End Date])) 

我最终希望在这两个表之间建立关系，使得：

I want to eventually make a relationship between these two table such that:

1）何时定义日期切片器范围后，将过滤与切片器重叠的作业（使用开始日期和结束日期列）

1) When the date slicer range is defined, the jobs that overlap the slicer are filtered (using the start dat and end date columns)

2）对于重叠的作业，如果开始日期<分钟（切片日期），然后将开始日期更改为分钟（切片日期）

2) And for the jobs that overlap, if the start date < min(slicer date) then start date change to min(slicer date)

推荐答案

您可以在员工预订2表中添加度量：

You can add a measure to the Staff Booking 2 table:

InRange = if(max('Date Range 2'[Date]) >= max('Staff Booking'[Start Date]) && MIN('Date Range 2'[Date]) <= min('Staff Booking'[End Date])),1,0)

作为一个示例，我将InRange量度添加到了视觉中，但是作为最后一步，您可以仅取1个一次就可以不在InRange列上进行过滤。

As axample I added the InRange measure to the visual but as last step you can filter not on the InRange column by only taking the once which are 1.

这篇关于表关系以确保当表中的日期重叠时使用日期切片器过滤表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！