Does DAX have any access to drillthrough filters?

[jprdynamicapps]

My client wants some summary data across just one dimension on a drill through page that has 3 drillthrough dimensions.
For example, drill through has person, location, and task, and most visuals on that page are for that combination of 3 things.

However, they would like to see one visual that shows all tasks for that person/location.

I can't figure out how to ignore one drill through filter in a DAX expression or even how to get the filtered values in DAX.

Any ideas, or is it not possible?

Thanks!

John

[AllisonKennedy]

I would need more specific examples to be more helpful, but have you tried using the ALL() function in DAX? The drillthrough filters act similar to other filters, and you can use the ALL(location) or ALL([people]) to ignore those filters and show all tasks for that person or location. This can go inside the CALCULATE function in DAX, with whatever expression you are already using. 

Again, if you can provide more specific examples, we can help further. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[jprdynamicapps]

Thanks, @AllisonKennedy 

I realize about ALL and using ALL with one column, which is really helpful.

What I didn't include (oops) was that I need to show just values in the dimension table that exist in my filtered fact table (e.g. all of the locations that the person has performed the task at).  Right now, I have all(location,locationame) in my table and a filter for countrows in my fact table > 0.  I get all values in locations despite this.

I use that countrows measure in my fact table to only include valid remaining values on a dimension when other dimensions have changed and it works great.  Not quite sure why it doesn't work in this situation, but that's what made me think the drill through filters are behaving differently.  I should mention that I've had some odd behaviors with that filter in places (sometimes not able to add it to a visual; sometimes able to add it, but not able to edit it).  Any ideas on this, please let me know.

If that can't work, I can presumably get distinct values out of my fact table while removing the filter on that one column.  This gets into an area of DAX that I find confusing.  In a situation like this, what do I need to create in DAX that can be placed in the field well for the table?  Do I use a calculated table?  There is a single-direction relationship between all of my measure and the fact table.

I've tried creating a calculated table with location unfiltered = CALCULATETABLE('fact personLocationTask',ALL('dim location'[location])), but this is returning all values from the fact table (not filtered by the person and task on the drill through page).

Appreciate the assistance.

John

[AllisonKennedy]

@jprdynamicapps  Are you able to send any screenshots of your model view so I can get an idea of the tables and relationships, and also of the fields pane so I can see which columns are calculated via DAX and which are coming from the query? 

I think a good solid understanding of DAX contexts might help you out, as you talk about relationships between measures and fact table, but the context of a measure in DAX is that it floats across the entire data model and is not actually attached to any table (we just place it in one to make it easy to locate).

Measures and columns can both be added to the fields well of a table visualization. Depending on what results you want, it may be required to have two unrelated tables both containing similar information in order to enable you to create a measure that does not filter/affect the other table, but I would probably create these tables in the query editor (not DAX) and still don't fully understand your data model/question so if you can give any more details that would be great!

[jprdynamicapps]

Thanks @AllisonKennedy I greatly appreciate your assistance.

I can't share my client's schema, but here's a sample that shows the issue.

https://www.dropbox.com/s/6xowokb1wdei74k/example.pbix?dl=0 

Schema is one fact table, "fact", and 3 dimension tables.

On Page 1, you see a matrix of the fact table along with slicers for the dimensions.

The slicers have "Rows in Fact" > 0 as a filter, so if you pick a value for a dimension, only the valid combinations from the fact table will show in the other slicers.

Pick a leaf row in the slicer and click drill through.

The 3 tables show the rows as filtered by the drillthrough as expected.

The location list on the right is displaying rows from a calculated table that is defined as 

All Locations = all(Location[Location]) that is filtered by "Rows in Fact" > 0, but it is showing all locations, not just ones found in the fact table (Only Living Room and Dining Room are in the fact table).

I'm not sure why this visual is not showing just the two from the fact table.

How can I get a list of just the valid locations from the fact table while keeping the drill through's filters on Person and Task?

Thanks.

John

[jprdynamicapps]

@AllisonKennedy ,

I can get All Locations to just show rows from the fact table by changing the definition to this:

All Locations = FILTER(Location,CALCULATE(COUNTROWS('Fact') > 0))

I'm not sure why it doesn't work when that filter is applied to the visual.

However, this is all locations in the fact table, not keeping the filters by person and location that are on the drill through page.

John

[AllisonKennedy]

@jprdynamicapps  I'm still unclear about the definition of which locations you want to have displayed, do you want all locations that are in the fact table or only locations that each person has worked? Which filters do we need to keep and which to ignore? In the example you sent each person only has one location in the fact table so I can add to it based on your description, but I'm still unclear if you want to keep filter for person or not?

Ulitmately, you need to look at the whole picture: Relationships, DAX context and filters.

The All Locations table is a Calculated Table, which now sits in the model and is already static before any filters are applied. By building the filter into the definition of that table, you can determine which Locations are shown, but these will always be the locations available in that table. 

Then you need to look at the relationship between the All Locations and Fact table. In the example you sent, there is no relationship, so these will always have the same number for 'Rows in fact' equal to the total number of rows, which is why that 'Rows in fact' filter does nothing for that visual on the drillthrough page. 

Hopefully some of that makes sense? Let me know your thoughts and questions from here.

Allison

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[jprdynamicapps]

Thanks, @AllisonKennedy for explaing re calculated tables being part of the model and not changing.

I updated my example with more fact rows and the countrows filter I mentioned above.

If you click Jack/Dining Room/Clean and go to the drillthrough, I want to be able to get a table with Dining Room and Kitchen because those are all of the locations in which Jack did Clean (e.g. keep person and task filters but lose location filter).  Make sense?

John

[AllisonKennedy]

Not knowing what else is in the report I'm not sure how this will affect other things, but is there a reason the location filter is in the drillthrough? If you delete the location filter from drillthrough and turn keep all filters to OFF that should acheive what you're looking for. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[jprdynamicapps]

@AllisonKennedy ,

No, I need the drillthrough with location as the primary purpose of the drillthrough page is specific to person, location, and task.

But, the client also wants to be able to show some data based on the other locations that this person performs the task in.

Thansk.

[AllisonKennedy · Accepted solution]

Ok, so you need to build a dynamic table that updates based on your drillthrough slicers so needs to be built in to the measure. 

Try this as a MEASURE: 

Visited Locations = IF(HASONEVALUE('All Locations'[LocationID]), IF(CONTAINS(FILTER(ALL('Fact'),'Fact'[PersonID]=SELECTEDVALUE(Person[PersonID])&&'Fact'[TaskID]=SELECTEDVALUE(Task[TaskID])),'Fact'[LocationID], SELECTEDVALUE('All Locations'[LocationID])), "True", "False"),"Error")

Then add that [Visited Locations] measure as a filter to the location table and filter for only True.

You will still need to be using the All Locations field in this location table so that it ignores the drillthrough filter for location, but you should be able to add other measures to it if you follow the same pattern as the Visited Locations measure above. 

Please mark this as solution if it helps. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[jprdynamicapps]

Thanks, @AllisonKennedy 

I was getting hung up on how to use a filtered table in a measure.  I wasn't realizing that I should use it to create a filter and then put that on a visual.

Great appreciate the help!

John

[AllisonKennedy]

@jprdynamicapps  You're very welcome! Thanks for the update, and sorry for the delays.

[jprdynamicapps]

Thanks.  I updated the example for anyone who may find this.

John