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

Filter a table with dates coming form another table

Hi,

I have a Table (Table1) with this columns (Time, shop, Start_time, End_Time) and a secund table (Table2) with the this columns (Time, shop, Sales). I'm making a dashboard that use the Table1 to filter a time series chart (Table2) for each shop between Start_time, and end_time. I need that each time that I press in a row in the Table1 the Time series chart change showing the time series with the time between (start_time, End_Time) for each shop.

Thanks a lot

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @afva_2000 

 

Create below measure and put it into the line chart's filter pane as a filter field. Set it to show items when value is 1. 

TimeFlag = 
var vStartTime = SELECTEDVALUE(tabla_pbi[start_time])
var vEndTime = SELECTEDVALUE(tabla_pbi[end_time])
var vTime = SELECTEDVALUE(super_tabla[Time])
return
if(HASONEVALUE(tabla_pbi[start_time]), if(vStartTime<=vTime && vEndTime>=vTime, 1, 0), 1)

21101503.jpg

21101504.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @afva_2000 

 

Create below measure and put it into the line chart's filter pane as a filter field. Set it to show items when value is 1. 

TimeFlag = 
var vStartTime = SELECTEDVALUE(tabla_pbi[start_time])
var vEndTime = SELECTEDVALUE(tabla_pbi[end_time])
var vTime = SELECTEDVALUE(super_tabla[Time])
return
if(HASONEVALUE(tabla_pbi[start_time]), if(vStartTime<=vTime && vEndTime>=vTime, 1, 0), 1)

21101503.jpg

21101504.jpg

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

amitchandak
Super User
Super User

@afva_2000 , Try a measure like this

 


sumx(filter(Table2,Table2[Shop] = max(Table1[Shop]) && Table2[time] >= min(Table1[Start time]) && Table2[time] <= max(Table1[End time])), calculate(sumx(values(Table2[Shop]), calculate(sum(Table2[Sales])))))

 

 

if this does not help

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

It didn't work, I created the measure in Table1, it didn't work, Then tried in table2, and in both and the results were the same.

 

I put a Sample data and pbi example here 

Dashboard pics (nothing happens, when yuou press on the table1 :

afva_2000_0-1634076487316.png

afva_2000_1-1634076539572.png

 

I just want that the time series show me the shops between this two dates (start_time, end_time). in SQL it would be more or less

Select

b.Time,

b.Shop,

b.Sales

from table2 as b inner join table1 a on 

a.start_time <= b.Time and

a.end_time >= b.Time;

 

 

Thanks a lot!!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.