Remove a page filter from a drill through

user_997474 0
Viewed 46

On a table of multiple columns, three columns form a hierarchical relationship:

Award Code (example: 505115)
Major Class code (example: 12XX)
Class Code (example: 1230)

enter image description here

There are three more columns: budget, actuals, and available budget (shown above).

On the second page of report, drill through is enabled and there are page filters:

Award Code
Major Class Code
Class Code

On this page, there is a table. If I click 1230 (red underline) on the first page of report and drill through to the second page, this is how the table looks like:

enter image description here

It is easy to see that this visual has three filters:

enter image description here

How can I remove the Class Code filter from this table, so that it displays all classes under a given Major Class Code. Using the above example, this is what I would like to have for the table:

enter image description here

UPDATE

This is the sample.pbix file in Dropbox

https://www.dropbox.com/s/vql16lyq40iccny/sample.pbix?dl=0

powerbi
2 Answers

You can't clear the drill through filters but you can work around it with a little trick. You create duplicate columns for award, major and class and you use those for your visual on your drill through page instead of the ones passed through in the filters on the drill through from your originating page. You need a measure to recreate the filter context you desire on your drill through page. Here is a video showing how to do it. If you have a sample .pbix, I can show you exact steps.

https://www.youtube.com/watch?v=frC3B_Dk3T8

user_183450370

  1. Create a new calculated table

    Drill Table = data

  2. On destination page, remove your old visual and create a new table visual using the fields from the new calculated table above.

  3. Create a drill measure

    Drill Measure =

    VAR a = SELECTEDVALUE(data[Major Class Code])

    VAR b = SELECTEDVALUE('Drill Table'[Major Class Code])

    RETURN IF(a = b, 1)

  4. Add the drill measure to the visual filter as follows.

enter image description here

Sample Link

user_183450370
Related