Forum Discussion

sebco's avatar
sebco
New Member
4 years ago
Solved

Date slicer with multiple dates and tables

Hello,   I am trying to filter data on dates based on a [createdon] field in multiple tables. I have seen several methods but I have used what I thought was the the simplest.   I have a slicer ba...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sebco 

    Are calendar and msfp_surveyinvite two separate tables with no relationship created ? I restored your scene, everything looks fine .

    (1)Create a Calendar date table ,and add a slicer with calendar date . Then create two measures to return the Max and Min date when change date in Slicer .

    Max Date = MAX('Calendar Date'[Date])
    Min Date = MIN('Calendar Date'[Date])

    (2)Then create a measure for data table to judge whether the date in data table is contained in slicer date range , if yes ,return 1 , no return 0 .

    Measure = IF(SELECTEDVALUE(Data[Date])>=[Min Date] && SELECTEDVALUE(Data[Date])<=[Max Date],1,0)

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi sebco 

Are calendar and msfp_surveyinvite two separate tables with no relationship created ? I restored your scene, everything looks fine .

(1)Create a Calendar date table ,and add a slicer with calendar date . Then create two measures to return the Max and Min date when change date in Slicer .

Max Date = MAX('Calendar Date'[Date])
Min Date = MIN('Calendar Date'[Date])

(2)Then create a measure for data table to judge whether the date in data table is contained in slicer date range , if yes ,return 1 , no return 0 .

Measure = IF(SELECTEDVALUE(Data[Date])>=[Min Date] && SELECTEDVALUE(Data[Date])<=[Max Date],1,0)

The final result is as shown :

I have attached my pbix file , you can refer to it .

 

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jimbob2285's avatar
jimbob2285
Icon for Advocate IV rankAdvocate IV
3 years ago

Hi Anonymous 

 

Sorry, i know this post is a year old, but I've had the same challenge and come to the same solution as you proposed, which works fine at a granular level, but I'm also summarising by person and it doesn't work at a summarised level... it's just showing a zero

 

I've added person to the data table and added another visual to the page to demonstrate the problem - I've added a screenshot below, but I can't see how to attach the actual PBIX file

 

Is there a way to resolve this, I'm hitting my head against a brick wall trying to work this out

 

Thanks

Jim