Is there a way to only apply filters to specific columns?

[Julian_S]

Hi everyone

I have a matrix in my report with a number of KPIs on the columns, and the organisation structure (companies, departments, people) on the rows. I've been asked if it is possible to exclude certain departments from the report, which of course I can do very simply using the filters pane, and just de-selecting the departments that aren't needed.

HOWEVER! 

The more complicated part is that the requestor only wants the department exclusions to apply to SOME of the report columns and not all. Is there any way to use the functionality in the standard filter pane but apply the action to a specified set of columns?

This is a simple example of what I would like to achieve, mocked-up in Excel to show the requirement:

The columns that are to be filtered and the ones that should remain unfiltered are static (i.e. I could define them and they won't change) but I'd like to try and do this using the filters pane if possible, so that the report user can freely-select which parts of the organisational structure to include or exclude as they need to.

Grateful for any hints on whether this is possible or not, and if so, how to achieve it?

Julian

[v-lili6-msft]

hi, @Julian_S 

You could try this way as below:

Step1:

Create three dim tables (companies, departments, people), Then create the relationship with fact table and keep all the relationship is inactive.

Step2:

Use fields from these three dim tables for slicer.

Step3:

For the columns that you want to be applied filter, you could use this formula to create a measure

Measure Qty = 
CALCULATE (
    SUM ( 'Table'[Qty] ),
    USERELATIONSHIP ( 'Dim Company'[Company], 'Table'[Company] ),
    USERELATIONSHIP ( 'Dim Department'[Department], 'Table'[Department] ),
    USERELATIONSHIP ( 'Dim Employee'[Employee], 'Table'[Employee] )
)

For the columns that you don't want to be applied filter, you could use this formula to create a measure

Measure Target = CALCULATE(SUM('Table'[Target]))

Step4:

Then when creating the matrix visual, use companies, departments, people from fact table.

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Julian_S]

Lin, thanks for your reply, I will try your suggestion and see if I can make it work for my circumstance. At the moment I do not have separate dim tables - everything is contained in one query result set as the data comes from a relational source, with columns for all the levels of the organsiation dimension and the measures (KPIs) like so:

Can I keep the data in one table like this and use a self-reference in the USERELATIONSHIP function, e.g. USERELATIONSHIP ('Table'[Company], 'Table'[Qty])? I'm very new to DAX so I don't yet understand its limitations.

Many thanks for your help

Julian

[v-lili6-msft]

hi, @Julian_S 

You could use New table to create these dim tables and then create the inactive relationship as above.

And it could not keep the data in one table like this and use a self-reference in the USERELATIONSHIP function.

Regards,

Lin