Date slicer with multiple dates and tables

[sebco]

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 based on a calendar table 'calendar'[Date] that works fine (see picture).

I have Min&Max variables that are picking up the date range on the filtered calendar table that also work fine (see picture).

I then try to use these Min&Max variables to change the value of a calculated column 'surveyinvite'[DateFilter] using the following formula to add a 1 when the msfp_surveyinvite'[createdon] is within the range and 0 when it is not.  This will allow me to add a page filter with 'table'[createdon]= 1 for all the tables:

DateFilter =

var mindate = MIN('calendar'[Date])

var maxdate = MAX('calendar'[Date])

return

if(('msfp_surveyinvite'[createdon])>=mindate && ('msfp_surveyinvite'[createdon])<=maxdate,1,0)

As you can see from the resulting surveyinvite table, my formula is not working and I am getting 1 in all the rows irrelevant of date.

Any help? I feel I am very close to a solution.

[v-yetao1-msft · Accepted solution]

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]

Hi @v-yetao1-msft 

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

[parry2k]

@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.

[parry2k]

@sebco thats' fine, you have one active, and many inactive relationships, and you can use userelationship function to activate the inactive relationship. You just have to create measures and then use it in the visualization and it should work. This is the right way to do it.

[sebco]

@parry2k Could you show me an example of the measure you would use for the table I have shown (assuming this one would not have an active relationship)?

[parry2k]

@sebco why you need to create this column, why not you have relationship set from date table to your other tables on created date, and add you select the date range, it will filter the data.  

[sebco]

@parry2k The dataset is from Microsoft Customer Voice with several different relationships.  The Calendar table (marked as a date table) will only let me create one relationship with the other tables I need to filter (surveyinvite, customerresponse, questionresponse).  Other relationships from the calendar table create primary/secondary issues which will not let me activate them without removing other relationships which I can't do.