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

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Adidas
Helper I
Helper I

Drill through with a date restriction

Hi All,
I have a report that presents purchase invoices on the item level (Page1 based on Table1).
for each item, I need to enable a drill through to the sales page (Page2 based on Table2)
and show only sales that took place after the purchase date of this item.
Since the relationship between table 1 and table 2 is many to many, I decided to build an intermediate table as so:

 






On the sales page, I enabled the "keep all filters" feature,
so I'm able to bring the purchase date from the main page (Page1) into the table on the drill through destination page (page2).
I was not able to build a measure that captures the passed filter and restrict the sales table only to sales that occurred after the purchase date.
can someone help me with that, please?

2 REPLIES 2
amitchandak
Super User
Super User

@Adidas , Something like this


Measure =
var _max = maxx(allselected(Table), Table[Purchase date])
return
calculate(countrows(Table), filter(All(Table), Table[Purchase date] >_max))

 

But better join purchase date with date table and use that

 


Measure =
var _max = maxx(allselected(Date), Date[date])
return
calculate(countrows(Table), filter(All(Date), Date[date] >_max))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks, @amitchandak,

It didn't work for me, since "Purchase table" and "Sales table" are only connected via the "Item" field (throughout an intermediate table), the moment I put the new measure inside the Sales table the table exploded.

 

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.