Store values of a Measure and use them for Filtering data

[Anonymous]

Hi, I have a situation where there are readings stored in one table for various equipment that include time of reading and also the Start and End times when the person took the various readings (lets say a Tour around the plant).  

The photos (image URL, caption, time of photo taken etc) are storedanother table. The readings time are different to the times of the Photos (we are talking mostly minutes differences). The two are linked via the UID of the  These all data is captured in three shifts (8 hr per shift X 3 shifts). I want to drill-down from a reading in time to get to the associated photos but as there is no direct relationship between Reading Time and Photo Time, I am getting all the photos displayed. On the drill-through page I managed to get the Tour Start and End time for a given reading. I want to use that Duration to filter out the Photos table such that photos taken only during that tour are shown, and not all.

My challenge: I want to know if I can somehow save those two values into Parameters and then filter the data (based on the two parameters). Is that possible?  

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

The calculation table cannot be used, and the calculation table cannot be affected by the slicer

According to your description, I create this data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _start=SELECTEDVALUE('Table'[start time])
var _end=SELECTEDVALUE('Table'[end time])
return
IF(MAX('Table2'[filming time])>=_start&&MAX('Table2'[filming time])<=_end,1,0)

2. Place the measure in the Filter, is=1, Apply filter.

3. Result:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu, many thanks for taking time to work on this example. I will try this soon and late you know.

[Anonymous]

Hi @v-yangliu-msft The solution you provided worked for me. There are two drill-through pages where I am navigating. One of them worked fine, the other one is coming empty even when there are photos taken during the time range. I will redo the drill-through filters on it and also recheck the measure I have set-up to ensure there are no mistakes. Thanks a lot for your help.

[Anonymous]

ok. so basically when there is no relationship involved and if I use MAX then it works but I need to use SELECTEDVALUE as on the drill through page if I getting those values(when I am using Measure to return the Start and End times). 

I am trying to create a copy of the filtered the Photos table but it is returning empty for some reason. Any idea what would be a solution to use the Selectedvalue from Tour Start and Tour End time inside a calculated table? 

FILTER (

('CP Photos(Area)'),

'CP Photos(Area)'[DateTaken] >= SELECTEDVALUE('DP Readings(Area)'[TourStartTime])

&& 'CP Photos(Area)'[DateTaken] <= SELECTEDVALUE('DP Readings(Area)'[TourEndTime])

[Anonymous]

Update: I think I got the logic on how to use those dates (from unrelated tables) in a measure from this link: Solved: Re: Storing Measure output per date - Microsoft Power BI Community 
I will update soon on how it goes. Initial test seems promising.