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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

sebco lets you have 3 columns in your transaction table, create date, due date, ship date and you have the relationship between these 3 dates with the date table, and the create date relationship is active, and other two are inactive. 

 

And now we want to have a count of orders by create date, due, and ship date so that we can see these 3 on the same x-axis, maybe month, year, or date. These are the measure we will create and then visualize, pick columns from date dimension and use these measures for values.

 

Create Date Order Count = COUNTROWS ( SalesTable ) --because this is active relationship

Due Date Order Count = 
CALCULATE ( 
    [Create Date Order Count],
    USERELATIONSHIP ( SalesTable[DueDate], DateTable[Date] )
)

Ship Date Order Count = 
CALCULATE ( 
    [Create Date Order Count],
    USERELATIONSHIP ( SalesTable[ShipDate], DateTable[Date] )
)

 

 

✨ Follow us on LinkedIn and  to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.