Date slicer not working

Power BI Data Model
1

Hi there,

I am trying to create a date slicer that contains 4 different options i.e. Today, Yesterday, 7 days, and 10 days option. I want to filter my other measures based on these 4 options. I created a table using following code for to have these dates:
let
TodaysDate = Date.From(DateTimeZone.LocalNow()),
Ranges = {
{“Today”, TodaysDate, TodaysDate, 1},
{“Yesterday”, Date.AddDays(TodaysDate, -1), Date.AddDays(TodaysDate, -1), 2},
{“Last 7 Days”, Date.AddDays(TodaysDate, -6), TodaysDate, 3}, // last 7 days including Today
{“Last 10 Days”, Date.AddDays(TodaysDate, -9), TodaysDate, 4}

         },
fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
    let
        DayCount = Duration.Days(EndDate-StartDate)+1,
        DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
        AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
        CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
    in
        CreateTable,

GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables)

in
Output

I connected the above table with date table and main fact table as shown in the picture:

image
image1602×705 115 KB

I used one of the field of the date slicer table to create drop-down list as shown in the following picture:

image
image1006×528 15.7 KB

When I choose the date value from the drop-down list it does not make any impact on the card visuals. I am not sure where am I making the mistake? Could anyone help me in fixing the issue. Sample file here
sample_data.pbix (734.0 KB)

1 Like
2

The Date Slicer table you created will not filter down into your table as you can see the direction of filter which I’ve highlighted. It cannot filter in the direction you want it to.

It would probably be easier if you added these columns into your main date table.

image

2 Likes
3

Hi @leo_89 ,

Please read the instructions in the referenced article, you’ll find in the post.

@DavieJoe is correct, stating this is an issue with how you’ve currently incorporated this table in your model. Once you’ve followed the instructions in the article - that issue will be sorted.

I hope this is helpful.

2 Likes
4

Hello @leo_89,

Thank You for posting your query onto the Forum.

I agree with @DavieJoe that it’ll not filter the results since the direction of the arrow is going other way. In this case, you’ll be required to set the “Cross Filter Direction” (exceptional case) to “Both” changing it from “Single”. Below is the screenshot of the relationship provided for the reference -

Relationship Status

Now, once you change the filter directions than it’ll start filtering the results. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results936×152 5.53 KB

If I remember this same technique was also used by one of our expert @JarrettM in Challenge - 4 where he had also kept the bi-directional relationship between “Dates” table and “Period” slicer. Below is the link provided for the reference from where you can check out his write up as well as download the file for the reference purpose.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: Please click onto the link in order to view the entire post and not onto the “expand/collapse” button.

Thanks and Warm Regards,
Harsh

2 Likes
5

@leo_89 ,

Here is the article from Chris Webb, where he explains the approach. This is where I got the original idea.

Thanks
Jarrett

2 Likes