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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FreeAzData
Regular Visitor

Drill Through on a measure (Tricky problem)

The Idea is to drill through on a visual that contains a measure (such as a treemap visual) and get to a page with a table visual with records from two different tables (in this case Events and Injuries). 

What I would like is a table visual with all the fields from the events and injuries table in it (in an outer join, so that all events are listed and all injuries are listed) and I want this table to list all the events counted by the measure that was drilled through on, and all its corresponding injuries.

 

Set up

I have a measure the 'Events day before', when a slicer selects a date, the measure will work out the events that occurred the day before.

I have put this measure in a Treemap visual.

FreeAzData_0-1679288683091.png

 

In the drill through page, I have created a table visual, with all the columns from the Events table, and the measure 'Events day before' which I have set to 'is 1' in the filters pane (otherwise it will show me events from the date selected).

FreeAzData_1-1679288841742.png

However when I add a column from the Injuries table, a cross join is performed. I'm not sure what is happening behind the scenes to cause this or how to stop it from happening. 

FreeAzData_2-1679288946943.png

 

Here is the data model for context:

FreeAzData_3-1679289002432.png

 

This can be accomplished easily enough if events and injuries table is outer joined in the data model. But if the Events and Injuries are in two separate tables in a one(Events)-to-Many(Injuries), I couldn't get it to work.

I have put a little more explanation in the provided PBIX file.

Here is a link to a folder with the PBIX file and the Excel Data.

 

Also from the data supplied, here are the dates with injuries the day before:

  • 20 March
  • 21 March
  • 22 March
  • 26 March
  • 28 March
  • 30 March
  • 31 March
  • 1 April
  • 2 April
  • 3 April
  • 5 April
  • 7 April

 

The aim is to get the proper drill through table with all events and all injuries by drilling through on the measure (e.g. drilling through on the treemap visual). 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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