Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter data based on calculation

Hi All, I'm new to power bi, and need help with the following scenario. We are getting device data , which lists alarms with start date time and end date time. Sample data shown below, where users w...
Need Help
  • mwegener's avatar
    mwegener
    5 years ago

    Hi Anonymous ,

     

    try this.

    ShowInPeriod = 
VAR RangeStart =
    MIN ( DateList[Date] )
VAR RangeEnd =
    MAX ( DateList[Date] )
VAR Alerts =
    FILTER (
        'EDM Overflow Data',
        ( RangeStart <= 'EDM Overflow Data'[Startdate]
            && RangeEnd >= 'EDM Overflow Data'[StartDate] )
            || ( RangeStart <= 'EDM Overflow Data'[EndDate]
            && RangeEnd >= 'EDM Overflow Data'[EndDate] )
            || ( RangeStart >= 'EDM Overflow Data'[StartDate]
            && RangeEnd <= 'EDM Overflow Data'[EndDate] )
    )
RETURN
    COUNTROWS ( Alerts )



mwegener's avatar
mwegener
Most Valuable Professional
5 years ago

Hi Anonymous ,

 

try this.

ShowInPeriod = 
VAR RangeStart =
    MIN ( DateList[Date] )
VAR RangeEnd =
    MAX ( DateList[Date] )
VAR Alerts =
    FILTER (
        'EDM Overflow Data',
        ( RangeStart <= 'EDM Overflow Data'[Startdate]
            && RangeEnd >= 'EDM Overflow Data'[StartDate] )
            || ( RangeStart <= 'EDM Overflow Data'[EndDate]
            && RangeEnd >= 'EDM Overflow Data'[EndDate] )
            || ( RangeStart >= 'EDM Overflow Data'[StartDate]
            && RangeEnd <= 'EDM Overflow Data'[EndDate] )
    )
RETURN
    COUNTROWS ( Alerts )



  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Marcus,

     

    Thanks for your reply. The solution works better than the switch I was using. I also tweaked your expression slightly as given below, by removing the date filter from the date table which is joined to the fact. I thought with this I can get away without creating a second date list , as I have currently.

    ShowInPeriod4 =
    var RangeStart = MIN('Calendar'[Date])
    var RangeEnd = MAX('Calendar'[Date])

    VAR Alerts =
    FILTER (
    CALCULATETABLE('EDM Overflow Data',REMOVEFILTERS('Calendar'[Date])),
    ( RangeStart <= 'EDM Overflow Data'[Startdate]
    && RangeEnd >= 'EDM Overflow Data'[StartDate] )
    || ( RangeStart <= 'EDM Overflow Data'[EndDate]
    && RangeEnd >= 'EDM Overflow Data'[EndDate] )
    || ( RangeStart >= 'EDM Overflow Data'[StartDate]
    && RangeEnd <= 'EDM Overflow Data'[EndDate] )
    )
    RETURN
    COUNTROWS ( Alerts )
     
    This works similar in terms of timing with your expression, but I have to include this in the list else it works as a normal slicer would. Is there a way to keep this in the list, but hide it from view ??
    • mwegener's avatar
      mwegener
      Most Valuable Professional
      5 years ago

      Hi Anonymous ,

       

      can you show the problem in screenshots?
      It should work as a filter on the visual or not?