Measure Derived from Slicer not Filtering Correctly

[Anonymous]

Hi everyone,

I have a slicer that is used to create a date range from which we filter contact records. The goal of this slicer is to determine if the bounds of a contact's start and end date are somewhere within the bounds or overlapping the bounds of the slicer's dates. For example, if you choose between Jan 1, 2020 and Jan 1, 2022 in the slicer, a record with a start date of Jan 1, 2021, and end date of Jan 1, 2050 would be returned.

To extract the bounds of the slicer, I use two measures. The problem is that it doesn't seem like these measures are having their values updated when they change when used with the FILTER function. My goal is to have their values change. Below is the code for the measures that get their values from the slicer and the table that I'm attempting to filter:

Selected Start Date =
Var Select_Slicer = MIN('date_selection'[Start and End Dates])
return Select_Slicer

Selected End Date =
Var Select_Slicer = MAX('date_selection'[Start and End Dates])
return Select_Slicer

Report Display =
var carrying_join = NATURALINNERJOIN(contacts, ogc_governancebodyassignments)
var carrying_join2 = NATURALINNERJOIN(ogc_governancebodies, carrying_join)
var carrying_join3 = NATURALINNERJOIN(ogc_constituencies, carrying_join2)
var carrying_join4 = NATURALINNERJOIN(ogc_positions, carrying_join3)
return FILTER(
    carrying_join4,
    ([Selected Start Date] >= [ogc_startdate].[Date] && [Selected Start Date] <= [ogc_reportingenddate].[Date]) ||       ([Selected End Date] >= [ogc_startdate].[Date] && [Selected End Date] <= [ogc_reportingenddate].[Date])
)

In the above, it seems like [Selected Start Date] and [Selected End Date] are static values but I would like them to change as the slicer changes. I have confirmed with cards however that the measure values do change in some contexts:

Thank you in advance for the help

[v-shex-msft]

HI @Anonymous,

Did these replies help for your scenario? If this is a case, you can consider accepting the suitable one to help others who faced a similar requirement to find it more quickly.

If not, you can feel free to post here with the detailed description and sample dataset.

Regards,

Xiaoxin Sheng

[AllisonKennedy · Accepted solution]

@Anonymous  You can try either using an iterator (MAXX, COUNTX, any of the functions that end in X) to provide table context. This creates a virtual column inside a measure. 

Or, you can cheat and use SELECTEDVALUE or MAX, but this will only work when used in the right context, such as in the table visual in your screenshot.

IF(([Selected Start Date] >= MAX([ogc_startdate].[Date]) && [Selected Start Date] <= MAX([ogc_reportingenddate].[Date])) ||       ([Selected End Date] >= MAX([ogc_startdate].[Date]) && [Selected End Date] <= MAX([ogc_reportingenddate].[Date])), 1, 0) 

[Anonymous]

Bingo! SELECTEDVALUE is exactly what I needed as I needed to evaluate those conditions on a per row basis with the visual filter. Thank you so much for the help!

[AllisonKennedy]

@Anonymous  Create a single measure that does all filters: 

IF(([Selected Start Date] >= [ogc_startdate].[Date] && [Selected Start Date] <= [ogc_reportingenddate].[Date]) ||       ([Selected End Date] >= [ogc_startdate].[Date] && [Selected End Date] <= [ogc_reportingenddate].[Date]), 1, 0) 

and filter for measure = 1

[Anonymous]

@AllisonKennedy  I've tried to implement as a measure but [ogc_startdate].[Date] and [ogc_reportingenddate].[Date] are not single values so I get the following error:

A single value for variaton 'Date' for column 'ogc_startdate' in table 'ogc_governancebodyassignments' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The solution for this seems to be calculated columns but I run into the same issue where slicer values are incorrect, probably because your documentation suggests that slicing happens after calculated columns. I'm not sure how to get around this issue given this behavior.

Thank you for the help as well as I'm very new to the PowerBI world.

[Anonymous]

@AllisonKennedy Thanks for the reply.

However, I'm having difficulty implementing the following logic using visual filters:

([Selected Start Date] >= [ogc_startdate].[Date] && [Selected Start Date] <= [ogc_reportingenddate].[Date]) ||       ([Selected End Date] >= [ogc_startdate].[Date] && [Selected End Date] <= [ogc_reportingenddate].[Date])

It seems that visual filters are restricted in their ability to combine "and" and "or" conditions in the way above.

Is there a way to achieve the logic above using visual filters?

[AllisonKennedy]

@Anonymous  You are trying to create a new TABLE that changes with slicers, but that's not how Power BI works. You need to create a new MEASURE that you can apply as a filter to the table visual. See if this post helps explain: Power BI Order of Operations 

[amitchandak]

@Anonymous , [Selected Start Date] and [Selected End Date] should change with slicer. Can share the use case where it is not changing

[Anonymous]

@amitchandak  Thank you for replying.

The use case I'm applying this to simply filtering out the result of "Report Display" using table visualization. To test out my theory that [Selected Start Date] and [Selected End Date] do not change, I simplified "Report Display" to the following:

Report Display =

var carrying_join = NATURALINNERJOIN(contacts, ogc_governancebodyassignments)

var carrying_join2 = NATURALINNERJOIN(ogc_governancebodies, carrying_join)

var carrying_join3 = NATURALINNERJOIN(ogc_constituencies, carrying_join2)

var carrying_join4 = NATURALINNERJOIN(ogc_positions, carrying_join3)

return FILTER(

  carrying_join4,

  [ogc_startdate].[Date] >= [Selected Start Date]

)


As you can see, I suspect that [Selected Start Date] is still 1970/1/1 despite being set to year 1999: