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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bingbadabom
Helper I
Helper I

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:

idstart_dateend_date
12022-02-02T00:00:002022-02-05T00:00:00

 

I have a second table containg rows with dates that occur between the start- and end-date.

idproductsalesdate
1a52022-02-02T02:00:00
2b102022-02-03T02:00:00
3c202022-02-06T02:00:00

 

What i want is to present visuals with the second table as a source..

How can i do that? I've tried searching similar solutions, but they always do some kind of summary of the data, and that's not what i need.  I need to make (for example) a column chart showing summary of sales per product within the date interval.

 

Edit: There's no relations between the two tables other than the dates

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

Hi , @bingbadabom 

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:

vyueyunzhmsft_0-1685065614726.png

(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:

vyueyunzhmsft_1-1685065655435.png

 

 

 

 

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

 

View solution in original post

3 REPLIES 3
bingbadabom
Helper I
Helper I

Most amazing, thank you so much!

v-yueyunzh-msft
Community Support
Community Support

Hi , @bingbadabom 

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:

vyueyunzhmsft_0-1685065614726.png

(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:

vyueyunzhmsft_1-1685065655435.png

 

 

 

 

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

 

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 😅).

bingbadabom_0-1685172778956.png

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.

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.