Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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).
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.
Here is the data model for context:
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:
|
|
|
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).
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |