Slicer for complex date range

[jlc211]

Hello,

New PowerBI user coming from essentially writing queries in SQL Server.  I'm trying to figure out the best way to handle some dynamic date ranges for a PowerBI report.  What I'd like to do is have a slicer(s) that filters the data across multiple visuals accordingly.  The problem is my date criteria isn't as simple as standard BETWEEN.  The WHERE clause from the original sql query I'm trying to translate looks like this

WHERE start_date < @startDate
AND (end_date > @endDate OR end_date IS NULL)

The PowerBI report will have 1 Table visual which is the master dataset that gets pulled in via Import Query. It will then have 4 visuals, all pie charts, that will pull from that master and would all need to be linked to this slicer.  

What I've tried:  I currently have two approaches both semi-working for this but I can't seem to get them working together.  In both approaches I have a calendar table setup as follows

Calendar = CALENDAR(Date(2015,1,1),DATE(YEAR(today()), month(today()), 1))

In my first approach I have a measure called "Range" that is tied to my table visual which filters out what I need quite nicely. 

Range = 
var endDate=FIRSTDATE('Calendar'[Date])
var actualDate=LASTDATE('Calendar'[Date])
return 
if(
    SELECTEDVALUE(TableName[start_date]) < actualDate
    &&
    (SELECTEDVALUE(TableName[end_date]) > endDate
    ||
    SELECTEDVALUE(TableName[end_date End])=BLANK()),
1,0)

The probelm is creating any relationships to the other pie chart visuals tables doesn't work which is the second approach I took.  I've tried combining the DAX for the Calendar and Range but have struggled and am not sure if that's possible. I've seen a slew of other approaches on these forums for similar things but nothing that has seemed to allow me to accomplish what I need.  Any thoughts or ideas would be greatly appreciated.  

[AllisonKennedy · Accepted solution]

@jlc211 

I haven't inspected your Range measure, but if you have it working for the table, then you're on the right track.

It's annoying, but you'll have to add the measure to 'Filters on this visual' (you can add filters there even if they aren't used in the pie chart / visual itself). Set that to only show values greater than 0.

The issue you may run into is that your pie chart has a different evaluation context than your table. So if that's the case, you'll need to create another measure and use that to filter the pie chart instead:

NewFilterRangeMeasure = 

COUNTROWS ( FILTER ( TableContextfromVisual , [Range] > 0 ) )

To get the TableContextfromVisual, either use VALUES function with the column that's unique in the visual you've used, or you may need to use summarize or another TABLE function: 

https://dax.guide/functions/table-manipulation/ 

[AllisonKennedy · Accepted solution]

@jlc211 

I haven't inspected your Range measure, but if you have it working for the table, then you're on the right track.

It's annoying, but you'll have to add the measure to 'Filters on this visual' (you can add filters there even if they aren't used in the pie chart / visual itself). Set that to only show values greater than 0.

The issue you may run into is that your pie chart has a different evaluation context than your table. So if that's the case, you'll need to create another measure and use that to filter the pie chart instead:

NewFilterRangeMeasure = 

COUNTROWS ( FILTER ( TableContextfromVisual , [Range] > 0 ) )

To get the TableContextfromVisual, either use VALUES function with the column that's unique in the visual you've used, or you may need to use summarize or another TABLE function: 

https://dax.guide/functions/table-manipulation/ 

[jlc211 · Accepted solution]

@AllisonKennedy  Thank you for the reply.  After reading through your suggestions I feel like I may have missed something crucial in my initial explanation. I'm using the DAX below to create a new table for my first pie chart.  My approach was going to be the same for each additional pie chart since the data is based on the query from my master dataset my plan was for that to be my "source of truth."

Gender = SELECTCOLUMNS(NewClients,"Name", [Name], "Gender", [Gender], "Start Date", [Start], "End Date", [[End])

Wih that said it occured to me that all I needed to do was to add an additional range measure using the same calendar fields but using the start and end date from my gender created table.  This seems to be working exactly as I'd hoped for and am surprised I couldnt' see the solution sooner.  Either way your suggestions indeed helped me arrive at this conclusion and for that I thank you very much.