Power BI:带有日期筛选器的假设参数 [英] Power BI: What-If parameter with a date filter

问题描述

我需要在一个表格上显示数据，该表格将根据过滤器上选择的日期显示六天前的数据.例如，如果在过滤器上选择的日期是2020/06/30"，我需要表格显示来自2020/06/29"、2020/06/28"、2020/"的数据06/27"、2020/06/26"、2020/06/25"、2020/06/24".

我尝试使用 What-If 参数，但无法将该参数链接到表中的日期列.请帮忙.谢谢.

解决方案

为了您的目的，您基本上需要一个断开连接的日历表.您可以为所有日期创建一个单独的自定义表格，如下所示-

<块引用>

考虑到您的日历表名称日期

<块引用>

考虑到您的事实表名称your_fact_table_name

Dates_disconnected =选择列(日期，"日期",日期[日期])

现在，使用新的自定义表 Dates_disconnected 创建您的 Date 切片器.

最后，在您的Base表中创建一个Measure，如下所示-

过滤器=VAR current_row_date = MIN(your_fact_table_name[date])VAR selected_date = SELECTEDVALUE(Dates_disconnected[Date])VAR selected_start_date = SELECTEDVALUE(Dates_disconnected[Date]) - 5返回如果(current_row_date <selected_date &&current_row_date>= selected_start_date,不"，是")

<块引用>

在上述措施中考虑了过去 5 天.你可以调整范围.

上面的Measure会给你一些输出如下-

您可以看到前 5 天(考虑到切片器中的选定日期)显示 No，而所有其他行显示 Yes.现在，只需使用度量 filter 应用视觉级别过滤器，并过滤掉所有行中包含 Yes 的行.

I need to display data on a table that will display data from six days ago based on the date selected on the filter. For example, if the date selected on the filter is "2020/06/30", I need the table to display data from "2020/06/29", "2020/06/28", "2020/06/27", "2020/06/26", "2020/06/25", "2020/06/24".

I tried to use a What-If parameter, but I can not link the parameter to the date column on my table. Please help. Thank you.

解决方案

For your purpose, You basically need a disconnected Calendar table. You can create a separate custom table for all dates as below-

Considering your calendar table name Dates

Considering your fact table name your_fact_table_name

Dates_disconnected =
SELECTCOLUMNS(
    Dates,
    "Date",Dates[Date]
)

Now, create your Date sclicer using the new custom table Dates_disconnected.

Finally, create a Measure as below in your Base table-

filter = 

VAR current_row_date = MIN(your_fact_table_name[date])
VAR selected_date = SELECTEDVALUE(Dates_disconnected[Date])
VAR selected_start_date = SELECTEDVALUE(Dates_disconnected[Date]) - 5

RETURN 
IF(
    current_row_date < selected_date && current_row_date>= selected_start_date,
    "No",
    "Yes"
)

considered last 5 day in the above measure. you can adjust the range.

The above Measure will give you some output as below-

You can see Previous 5 day (considering selected date in slicer) is showing No and all other row is showing Yes. Now, just apply a visual level filter using the measure filter and filter out all rows having Yes in the row.

这篇关于Power BI:带有日期筛选器的假设参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！