Single slicer but with multiple filter conditions

[Lucian]

Hello,

I need a report to be as simple as possible for users and I don't see if this would possible.

I have list of transactions for multiple departments:

Some of the transactions are inter-department transactions (marked with TRUE on IDT - column). Depending on the user's choice they would like to show or hide the IDT transactions, but usually the IDT transactions should be excluded.

Now there are two kind of reports that user want to see - Department Report (A or B or C) or Consolidated Report (All Departments).

The problem is that the inter-department transactions should be "excluded" depending on the report kind and the "IDT Level":

- If the report type is by department (for example Dep. A) it shoud display the records where:

Department = A,
IDT=False,
IDT=True && IDT Level <> A

- If the report is a consolidated one, it shoud display the records where:

IDT = False

Basically on this last report it should exclude all the IDT transactions.

My issue is that I have to obtain these reports with only 2 slicers:

- One slicer listing all of the departments (A/B/C) and with no department selected (or all selected) it means I have to display the "consolidated report", and if only one department is selected I have to display that "department report".

- Because the user may want to see or not the IDT transactions, I need a second slicer to exclude or not the IDT transactions values but depending on the report type.

Is this even possible?

Kind Regards,

Lucian

L.E.: I have to change the tables with pictures otherwise were displayed badly.

[flath]

Hi @Lucian

Yes, it's possible!

But you'll have to create a new column and new table in order to make it happen.

First, create a new column where:

IF(IDT = FALSE, 1, IDT = TRUE && IDT_LEVEL = "CONSO", 2, 0 )

Then, create a new table as following:

ID	Description	Key
1	By Department	1
2	By Department (IDT)	2
3	Consolidated	1
4	Others	0

Then, you'll create a relationship many to many between the two table using the field "Key".

**** I edited my reply because I forgot to include this step ****

You'll have to create a second table as following and create a relationship 'one-to-many' between the fields "Descriptions".

ID	Description
1	By Department
2	By Department (IDT)
3	Consolidated

Doing so, now you can create a slicer in your dashboard using the field "Description" of your SECOND table.

Regards.

[Lucian]

Hello @flath 

And firstly I would like to thank you for your time spent on helping me.

Trying your solution I have realised that is not achievable with a single slicer as I have stated in the original message title, but with two, so I try to explain more, and attach a PBIX file.

What I would like to achieve as design is like in the following picture, except the folowing:

- for "report type" slicer I have used directly the Department column, so when I select one of the A/B/C departments I will see all the records for that specific department, and with no selected department I will have all the records for the "consolidated" report.

- for excluding the "inter-department transactions" (IDT) I have used directly the column "IDT Level" that should indicate at which "level" the IDT transaction should be excluded.

 

My problem resides in "instructing" users on what "slicer combinations" they have to use to achieve the 4 or 6 possible needed "views", which I will try to describe below.

View 1: Department level report with all of the transactions (INCLUDING IDT)

- Here user will select just a single department A/B/C and in this picture because the slicer is based on the Department column all is straightforward.

View 2: Department level report EXCLUDING the IDT for that specific department

- Here selecting just one of the A/B/C departments, to exclude that specific IDT transactions it seems that IDT Level column should be "blank" or "CONSO" ("conso" is kind of "top level" report with all "consolidated" departmens)

- in this case keeping the IDT Level slicer on "blank" or "CONSO" will let the user select any other department B/C and see that department transaction without the "internal transactions":

- but here, if the user want to check ONLY the "IDT" transactions specific to this department the user will have to select the same value for "IDT Level" and "Department" (or more simpler -  just the from the "IDT Level" slicer the department name)

View 3: The consolidated report - meaning no department will be selected (or ALL of them should be selected) - but EXCLUDING all the IDT transactions

- Here selecting the IDT Level = blank all of the IDT transactions will be excluded at all levels (not just at the department level)

View 4: The consolidated report but only with IDT transactions

- Here using the IDT Level slicer I should select all values except blank

So I will have to reformulate my question: Is it possible that the "IDT Level" slicer to be transformed in a way that will have only two values TRUE/FALSE to be able to include/exlude IDT transactions but this will depend on how the user will select the "Department" slicer (here no department selected will mean that is a "consolidated report") so I could cover the four views mentioned above?

In fact would be 6 views if I will count also the "IDT only" transactions when a department is selected or none selected (the consolidated report).

Here is the file I have played with if you consider it helpful:

Filter V1-flath.pbix 

Thank you,

Lucian

[flath · Accepted solution]

Hi  @Lucian ,

I've made some changes on your pbix file.

Filter V1-flath.pbix 

The new version filters as you've mentioned and also have an explicit option in order to let the users know what to do when they want to see Only IDT By Department Transactions.

Your views now are definied by filter as:

View 1: the user will just have to select the Department and keep the Type of Report Filters unselected.

View 2: By Department (no IDT)

View 3: Consolidated (no IDT)

View 4: Consolidated (IDT only)

View 5: By Department (IDT Only)

View 6: Full Report, in this case, the user must clear all the filters.

Let me know if that's what you were looking for.

[Lucian]

Hello @flath ,

Thank you for your help. I think this is what I need. 👍

Or usable enough for my users 😉

Kind Regards,

Lucian