Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bluefire
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
v-xuding-msft
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
v-xuding-msft
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.

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

amitchandak
Super User
Super User

@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 , I am taking date from a slicer and then filtering it out on the table

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.