Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter Measure Between Dates

I have data which has a from and to date, with the date it is created in the from and the date its no longer active in the to column. I created a filter measure so I can selected a date from a disconnected date table, and if the risk created date is before the selected date and the end date is after then show the data. Also if the risk is still ongoing the to date is blank (null).  This is the measure I created;

Active Threats = IF([Selected Date]>MAX('Risk Data'[FromTT])&&IF(ISBLANK(MAX('Risk Data'[ToTT])), TRUE(), [Selected Date]<MAX('Risk Data'[ToTT])), 1, 0)

I then filter my table using this measure.
 
 
This is my data, I'm expected when I filter the date to before the 16th of jan, for the 1 extreme and 1 moderate risk to drop off.
 
However this only happens if i keep the date column in there, otherwise all the moderate risks drop off. Any ideas why this is happending? Test data Here: https://drive.google.com/file/d/1iEN5Iz5U9h0pEPf_bRZw9x3LFeGTClYC/view?usp=sharing
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I managed to created a count measure that works as I would like;

    Count of Active =

    VAR __SelectedDate = [Selected Date]
    Return
    CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

    But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    Anonymous wrote:

    I managed to created a count measure that works as I would like;

    Count of Active =

    VAR __SelectedDate = [Selected Date]
    Return
    CALCULATE(COUNT('Risk Data'[Id]),__SelectedDate>'Risk Data'[FromTT], IF(ISBLANK('Risk Data'[ToTT]), TRUE(), __SelectedDate<'Risk Data'[ToTT]))

    But I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID

    I have test on my side, This formula works well, and what is " I'm not sure how'd I go about turning this into a true/false filter measure of each risk ID"?

     

    Regards,

    Lin

  • Anonymous's avatar
    Anonymous
    6 years ago

    Ah sorry, I've just realized if I use my count of acting as the visual level filter, I just need to set it to is greater than 0 to get the effect I was looking for.

    The previous response about setting the filter to true was confusing me since it wasn't an IF measure.

15 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    Sad to say, I cannot figure out exactly what is going on.

    I am posting the version of your measure I rewrote with as many VARs as possible

    Active Threats =
    VAR max_date = MAX('Risk Data'[FromTT])
    var sel_date = [Selected Date]
    var is_selected = [Selected Date]>max_date
    var is_blank = ISBLANK(max_date)
    var false_test = [Selected Date] < max_date
    var result = IF(is_selected && IF(is_blank, TRUE(), false_test), 1, 0)
    return result
    I tried returning the different vars to figure out what the problem was, but could not see what what happening.
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      kentyler Thanks for taking a stab at it, I think what is going on is that when I only have the risk and count in the table, date filter inside my measure is using the 16th of jan for all moderate risks since it uses MAX(date) its picking up the one out of date range for all moderate risks. Not too sure how I would edit my measure to evaluate row by row though.

      • kentyler's avatar
        kentyler
        Solution Sage
        6 years ago

        your selected date measure is reading from the slicer, and if the slicer does not have a value it defaults to the max of the date table... you can test this by just hitting the eraser on the slicer and removing any selection from it.

        Selected Date = SELECTEDVALUE('Date Table'[day_date], MAX('Date Table'[day_date]))
        so you need to think about how you want your measure to work if there is no date range selected.