Forum Discussion

Bluefire's avatar
Bluefire
Regular Visitor
6 years ago
Solved

Filtering in a historized table

Hello guys,

 

I am sorry to bother you with this question, but as i am fairly new to Power BI i dont really have a clue on how to proceed. The situation is as follows: i have a historized table where multiple valid from and valid to collumns exists. What i want to achive is that i can filter for a date and only the values are shown which where valid. So in general i need to filter between two date collumns if the date is on or after vaild from and on or before valid to. 

This example may not be the most complex one but i think it will do to get my point across. If i now select the 26.05.2017 as da filter it should only show the colored line. So i would need to check each pair of valid from and valid to column if the choosen date is between Valid from and valid two and then show only the correct line.

 

Can someone help me?

 

Kind Regards,

Marc

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Bluefire,

     

    You could drag the column "Valid From4" into slicer to filter your date.  Or you can create a date table and use a measure to achieve your purpose.

    The details are as follows:

     

    Firstly, you can create a Calendar table which contains all continued date :

     

    1. Click New table in Modeling Tab

     

    1. Create a date table as below:
    Date = CALENDAR(MIN('Table'[Valid From4]),MAX('Table'[Valid To5]))

     

    1. Use a Slicer, you may choose list, because it is convenient to select a specific values

     

    1. Find New Measure on Home Tab:

     

    Create a Measure like below:

    Measure =
var selectedvalue = SELECTEDVALUE( 'Date'[Date])
var a = CALCULATE(MAX('Date'[Date]),FILTER('Date',selectedvalue >= MAX('Table'[Valid From4]) && selectedvalue < MAX('Table'[Valid To5])))
return
IF(ISFILTERED('Date'[Date]),a)

    1. Add the Measure into the table value. If the date is between From4&To5, it will appear in Measure Column, when you select a date in Slicer.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Bluefire , You have measure like this and use is conditional formatting of each column for a >0

     

    measure =
    var _max = maxx(allselected(date),Date[date])
    return
    calculate(countrows(Table), filter(Table, (Table[validto]<=_max && Table[validfrom]>= _max )
    && (Table[validto2]<=_max && Table[validfrom2]>= _max )
    && (Table[validto2]<=_max && Table[validfrom2]>= _max ) ))

     

    Depending on need && in bold can be Or ||

    • Bluefire's avatar
      Bluefire
      Regular Visitor
      6 years ago

      Hi amitchandak ,

       

      unfortunately i think i need a bit more details on what to do exactly as i am completly new in Power BI.

       

       

      • TomMartens's avatar
        TomMartens
        Super User
        6 years ago

        Hey Bluefire ,

         

        the challenge you are facing has a name, it's called event-in-progress.

        This blog https://blog.gbrueckl.at/events-in-progress/

        explains how you can tackle a specific situation and has links to all the relevant posts. One of my favorite articles is this one:

         http://sqljason.com/2012/11/classifying-and-solving-events-in.html

         

        You have to be aware that there is no "golden" solution for this problem, as it also depends on the distribution of your data, the distance between the validfrom and validto.

         

        For this reason, I recommend that you need to read the articles, think about what fits your data model best. And it's also a good idea to prepare some sample data that still reflects your data model, upload the pbix to onedrive or dropbox, and share the link. If you are using excel to prepare the sample data, don't forget to share the xlsx as well.

         

        Regards,

        Tom

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi Bluefire,

     

    You could drag the column "Valid From4" into slicer to filter your date.  Or you can create a date table and use a measure to achieve your purpose.

    The details are as follows:

     

    Firstly, you can create a Calendar table which contains all continued date :

     

    1. Click New table in Modeling Tab

     

    1. Create a date table as below:
    Date = CALENDAR(MIN('Table'[Valid From4]),MAX('Table'[Valid To5]))

     

    1. Use a Slicer, you may choose list, because it is convenient to select a specific values

     

    1. Find New Measure on Home Tab:

     

    Create a Measure like below:

    Measure =
var selectedvalue = SELECTEDVALUE( 'Date'[Date])
var a = CALCULATE(MAX('Date'[Date]),FILTER('Date',selectedvalue >= MAX('Table'[Valid From4]) && selectedvalue < MAX('Table'[Valid To5])))
return
IF(ISFILTERED('Date'[Date]),a)

    1. Add the Measure into the table value. If the date is between From4&To5, it will appear in Measure Column, when you select a date in Slicer.

     

    • Bluefire's avatar
      Bluefire
      Regular Visitor
      6 years ago

      Thank you guys some much for your suggestions.

      I was able to create the visual for one Table. Is it somehow possible to youse a measure as a global filter that it effects all visuals on the same page? Else i would have to include this measure in every visualisation and i dont think that makes much sense.

       

      Kind Regards

      Marc