Forum Discussion

dmelton's avatar
dmelton
Regular Visitor
6 years ago
Solved

Measure Not Passing Through Date Slicer Value

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?

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      dmelton
      Regular Visitor
      6 years ago

      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's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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