Dynamic Filtering

[IntaBruce]

I wasn't sure what to call this thread because it's difficult to explain in a few words.  Here is my challenge:

I want to build reports on a Fact Table called ExhibitWorkOrder, and I want to filter those reports by Exhibit Type, the details of which are held int a Dimension Table called ExhibitType.
Some Exhibits are "Mobile Devices" while others are "Computer Devices", and some are neither.  However, which of these three groups an exhibit type is a member of is not stored in the dataset.
Is it possible to create report filters for these groups using user controlled slicers?

The approach needs to be dynamic so that membership of the groups is controlled by the person viewing the report because

a) I cannot determine the group in advance in order to code it in DAX, and
b) more exhibit types may be added to the ExhibitType dimension table in future and I need to avoid having to amend the report and republish it.

Example data:

ExhibitType Table

Exhibit Type	Description
1	Laptop
2	Workstation
3	Keyboard
4	Monitor
5	Server
6	Mobile Phone
7	PDA
8	iPhone
9	Macbook

ExhibitWorkOrder Table

ExhibitRef	Exhibit Type
001	5
002	6
003	8
004	6
005	6
007	1
008	2
009	1
010	1

There are other columns in both of these tables that will be used in the report but I have not included them here for simplicity.

As you can see, some types are obviously types of computer, some are mobile devices and some are neither, however this information is not in the dataset and I  want the person viewing the report to be able to define the groups using slicers (or something else?), but not an external lookup table like an excel workbook that will require ongoing maintainanace.

Is there an approach that will make this possible?

Many thanks

[v-saisrao-msft]

Hi @IntaBruce,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.if not please share the sample pbix with the expected output so we can help to resolve the issue.

Thank you.

[danextian · Accepted solution]

This is hard to solve because the data you need simply isn’t there. The best you can do is anticipate the type based on keywords in the description, but even that won’t always be reliable. DAX and M have limits — without a clear underlying logic to identify each type, there’s only so much they can do.

[Shubham_rai955]

Yes, you can let users dynamically group Exhibit Types without advance coding or a static lookup.

Solution:

• Create a slicer or filter visual using the ExhibitType table, where users select which types belong to each "group".

• Create a disconnected “Group Parameter” table with these group names: “Mobile Devices”, “Computer Devices”, “Neither”.

• Let users pick types in slicers for each group (using multiple slicers, one for each group).

• Use DAX to create a calculated column or measure to check which ExhibitType is selected in which parameter slicer, and then set the group dynamically for the report visuals.

• Because this is dynamic, when a new Exhibit Type is added, it automatically appears for selection in the slicers,no need to republish.

This lets users define groups on the fly, keeps report logic flexible, and needs no ongoing maintenance of external files. All group membership is controlled in the report by the end user.

[IntaBruce]

This sounds encouraging, but I don't quite follow what you are proposing, could you elaborate a little please?

[v-saisrao-msft]

Hi @IntaBruce,

Have you had a chance to review the solution we shared by @danextian ? If the issue persists, feel free to reply so we can help further.

Thank you.

[gopy6243]

Yes, you can do it.

Create a small disconnected table with your 3 groups (Mobile Devices, Computer Devices, Other), map each one to the ExhibitType values you want, and then use a measure with TREATAS to push that selection onto ExhibitType[ExhibitTypeDescription].

Your visuals will then filter ExhibitWorkOrder correctly without any hard-coding.

[Praful_Potphode]

Hi @IntaBruce ,

can you try creating a disconnected table and creating groupings in Power Query?

Thanks and Regards,

Praful

[vivmueller]

Is it possible to use a logical ID for the Exhibit Type column? Like instead of only 1, 2, 3... then to use MD-01 (mobile devices), CD-02 (computer devices) and so on? So anytime new devices are added, you directly add this information to the row.