Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter table based on dates from another table

I have one table that i use Slicers on to reduce it to one now. It looks something like this: id start_date end_date 1 2022-02-02T00:00:00 2022-02-05T00:00:00   I have a second table...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    Thanks for your sample data first!

    According to your description, you want to filter the table by the slicer .

    Here are the steps you can refer to :
    (1)This is my test data:

    (2)We can create a measure like this:

    Measure = var _start_date = MAX('Table1'[start_date])
var _end_date = MAX('Table1'[end_date])
var _t = FILTER('Table2','Table2'[date]>=_start_date && 'Table2'[date]<=_end_date)
return
SUMX(_t,[sales])

     

    (3)Then we can put the fields we need on the visual and the result is as follows:

     

     

     

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

     

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

Hi , Anonymous 

Thanks for your sample data first!

According to your description, you want to filter the table by the slicer .

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a measure like this:

Measure = var _start_date = MAX('Table1'[start_date])
var _end_date = MAX('Table1'[end_date])
var _t = FILTER('Table2','Table2'[date]>=_start_date && 'Table2'[date]<=_end_date)
return
SUMX(_t,[sales])

 

(3)Then we can put the fields we need on the visual and the result is as follows:

 

 

 

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Just a follow up. Supposed the id for the first table is not unique. Due to some joins in power query they are duplicated. Your suggested solution still works with this change.
But there's a new colum referencing a third table by value_id (i could not find a product/sales analogy for this 😅).

So if the row with id 1 from the first table is selected, i need to filter out values in the third table that has 'value_id' 1 and 2. I could only get your example to work with one of the values.