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

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

    • dmelton's avatar
      dmelton
      Regular Visitor
      6 years ago

      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!