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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Store values of a Measure and use them for Filtering data

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?  

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

v-yangliu-msft_0-1615451314695.png
v-yangliu-msft_1-1615451314703.png

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.

v-yangliu-msft_2-1615451314738.png

3. Result:

v-yangliu-msft_3-1615451314743.png

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.

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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:

v-yangliu-msft_0-1615451314695.png
v-yangliu-msft_1-1615451314703.png

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.

v-yangliu-msft_2-1615451314738.png

3. Result:

v-yangliu-msft_3-1615451314743.png

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
Not applicable

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

Anonymous
Not applicable

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
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors