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
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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors