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

kentyler's avatar
kentyler
Solution Sage
6 years ago

this is what I get when the date slicer is blank 

 

you seem to only be using one of your date columns, so each risk is attached to a single date... have you considered creating a relationship between that date column and your date table and just letting the slicer do the work.

all the risks show up, even without the date column being included

Anonymous's avatar
Anonymous
Not applicable
6 years ago

The issue with creating a relationship for the date is that I have several other tables with similar data with from and to date ranges, that all relate to each other, so I cant relate the date table to each other without creating circular references

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    I would be curious to see the relationship diagram

    If you think about your several tables as fact tables... they should all be able to be joined by a single date table that had a one to many relationship to all of them

    If you want to relate the the same date column in each table.

    If you want to also relate the other date column, the one you don't seem to be using in your example, I can see that you might need to then have a second date table to relate to all of those columns. Then any filters or slicers should work on all the "fact" tables independantly.
    The other option might be.. and I'm reaching here. To have tables which had the error condition and one record for each day it was operative. Then answering questions like, how many errors of each type were open on a certain day would be straightforward, without having to do a lot of equal to or less than style of logic.
    I'd love to have a more extended conversation with you about the whole problem.

    • Anonymous's avatar
      Anonymous
      Not applicable
      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
        Community Support
        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
      Not applicable
      6 years ago

      kentyler 

       

      The six highlighted tables each have from and to dates.

      • kentyler's avatar
        kentyler
        Solution Sage
        6 years ago

        A lot of them seem to be history tables... are they all histories of the same event ?