Measure Not Passing Through Date Slicer Value

[dmelton]

Let me just preface with the fact that this is my first PowerBI dashboard. I have since realized that I did not set up my data model in the optimal way. A solution is to create a relationship to my calendar table, but that would require me to rebuild all my measure and dashboard, so I am attempting to salvage the work I have already done.

Desired output: Distinct count of users who responded between the date range of the slicer. The slicer is linked to my calendar table.

I created these measures to pass through the values of the slicer.

DateSlicerMin = MIN(Calander_Lookup[Date])

DateSlicerMax = MAX(Calander_Lookup[Date])

They seem to be working correctly.

Now for some reason when I use them inside another measure, they do not work correctly.

Inquiry Dateslicer = CALCULATE(DISTINCTCOUNT('Campaign_Member_Contacts'[LookupID]),
                            FILTER('Campaign_Member_Contacts',
                                ('Campaign_Member_Contacts'[FirstRespondedDate] >= [DateSlicerMin] && 'Campaign_Member_Contacts'[FirstRespondedDate] <= [DateSlicerMax])
                                )
                            )

To test, I created the same measure using "hardcoded" dates and got the correct output, so I at least know the measure is correct.

Inquiry Hardcode = CALCULATE(DISTINCTCOUNT('Campaign_Member_Contacts'[LookupID]),
                            FILTER('Campaign_Member_Contacts',
                                ('Campaign_Member_Contacts'[FirstRespondedDate] >= DATE(2020,4,1) && 'Campaign_Member_Contacts'[FirstRespondedDate] <= DATE(2020,6,30))
                                )
                            )

I manually filtered the dataset and know for a fact the "hardcode" version of the measure is correct. Any thought on how to get the slicers to work to make it dynamic?

[Anonymous]

@dmelton 

I'm not sure if I understand, but couldn't you just make a filter on the page with either Advanced Filtering or Relative Filtering?

Then just make a Card visualization, add your users column and change it to distinct count?

[dmelton]

Apologies if my wording was confusing.

Unfortunately I need the filter to be dynamic with the date slicer so the values update when the user changes the date range. I was able to get it to calculate correctly by nesting the formula from the "DateSlicerMin" & "DateSlicerMax" measures inside the Inquiry count measure.

Inquiry Dateslicer = CALCULATE(DISTINCTCOUNT('Campaign_Member_Contacts'[LookupID]),
                            FILTER('Campaign_Member_Contacts',
                                'Campaign_Member_Contacts'[FirstRespondedDate] >= (MIN(Calander_Lookup[Date])) && 'Campaign_Member_Contacts'[FirstRespondedDate] <= (MAX(Calander_Lookup[Date]))
                                )
                            )

It's essentially the exact same formula so I am stumped why this works and the other way does not.

But at least I learned a valuable lesson about data modeling and what not to do 🙂

[mahoneypat · Accepted solution]

Try storing your slicer values as variables first.

Inquiry Dateslicer = 
var mindate = [DateSlicerMin]
var maxdate = [DateSlicerMax]
Return 
CALCULATE(DISTINCTCOUNT('Campaign_Member_Contacts'[LookupID]),
                            FILTER('Campaign_Member_Contacts',
                                ('Campaign_Member_Contacts'[FirstRespondedDate] >= mindate && 'Campaign_Member_Contacts'[FirstRespondedDate] <= maxdate)
                                )
                            )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dmelton]

This works, (but unfortunately will still require me to edit all my measures that use the DateSlicerMin/Max). Will still mark as solution.

This is good to know for nesting with more complex measures. Thanks!