PowerBI 筛选器-在开始和结束日期之间选择日期 [英] PowerBI filter- selected Date between Start and End date

问题描述

我正在从包含开始和结束日期的数据集构建 PowerBI 报告.我想根据包含另一个表中选定日期的行来过滤数据集.

I'm building a PowerBI report from a dataset that contains start and end dates. I'd like to filter the dataset based on rows that would encompass a selected date in another table.

此处的屏幕截图显示了一个示例.我想单击右侧表格中的日期，然后过滤左侧表格，其中所选日期介于开始日期和日期之间.结束日期.

The screenshot here shows a sample. I want to click a date in the table on the right and have the table on the left filtered where the selected date is between the start & end date.

我使用列和度量尝试了几种不同的方法，但一直无法确定.我还尝试从引用所选日期的 DAX 表达式创建一个新表，但这导致了错误.

I've attempted several different things using columns and measures, but I haven't been able to nail it down. I also attempted to create a new table from a DAX expression that references the selected date, but that caused errors.

如何根据所选日期介于开始和结束日期值之间动态过滤数据集?

How can I dynamically filter the dataset based on the selected date being between the start and end date values?

推荐答案

创建度量以检查行是否与所选日期范围重叠:

Create a measure to check whether a row overlaps the selected date range:

Date Included = 
IF (
    FIRSTNONBLANK ( Table1[Start Date], 1 ) <= MAX ( 'Calendar'[Date] ) &&
    FIRSTNONBLANK( Table1[End Date], 1 ) >= MIN ( 'Calendar'[Date] ),
    "Include",
    "Exclude"
)

将此度量添加为可视化的过滤器，其中 Date Included 是 Include

Add this Measure as a filter on your visualisation, where Date Included is Include

现在您可以过滤日历表(过滤到单个值或范围)，并且只会显示事实表中的重叠行.

Now you can filter your Calendar table ( to single value, or range), and only overlapping rows from your fact table will be displayed.

请参阅 https://pwrbi.com/so_55925954/ 获取工作示例 PBIX 文件

See https://pwrbi.com/so_55925954/ for worked example PBIX file

这篇关于PowerBI 筛选器-在开始和结束日期之间选择日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！