cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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.

 

Bluefire_0-1594626205743.png

Can someone help me?

 

Kind Regards,

Marc

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

v-xuding-msft_1-1594720019262.png

 

  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

 

5.png

  1. Find New Measure on Home Tab:

3.png

 

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.

4.PNG

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

@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 ||



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak ,

 

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

 

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@Bluefire , I am taking date from a slicer and then filtering it out on the table



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Community Support
Community Support

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

v-xuding-msft_1-1594720019262.png

 

  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

 

5.png

  1. Find New Measure on Home Tab:

3.png

 

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.

4.PNG

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors