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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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

Top Kudoed Authors