cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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
Highlighted
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!

Highlighted

Hi @amitchandak ,

 

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

 

 

Highlighted

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
Highlighted

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

Highlighted
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

Highlighted

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors