Parameters - filter issue. Bridge table?

[RafalDA]

Hi all,

I have quite big table with work orders (approx 40k rows). A snipped would look like that:

UID	DateTime	Status	Shift	Unit	Machine
WO001	01/01/2024 13:55	In progress	A	Unit 1	A1
WO002	01/01/2024 12:17	Completed	B	Unit 2	B2
WO003	02/01/2024 5:57	Completed	B	Unit 1	A2
WO004	03/01/2024 21:39	In Progress	A	Unit 2	B3
WO005	03/01/2024 21:45	Not started	B	Unit 2	B2

Now I have few more tables that determine what could be displayed for some of this fields (could, which means not necesarly there is anything for that in the main table, but I still want to display it, like Status "Approved" - none of that is in the data table but I still want to have ability to select it). So i have tables:

Status

Not Started

In Progress

Completed

Approved

Shift

A

B

C

D

Unit

Unit 1

Unit 2

Unit 3

Unit 4

Machine	Unit
A1	Unit 1
A2	Unit 1
B1	Unit 2
B2	Unit 2
B3	Unit 2

I've made relationships between this support tables and the main data table, I created field parameters based on the columns from each table and they work fine. But when I select 2 values (e.g. Status and Shift) and want to display list of fields (to filter some of them) for these parameters it throws an error that I can't do that due to lack of relationship. In other words I would like to Select Status and Shift in one slicer and then in second slicer select certain values of selected fields (In Progress and Shift B). It works fine with only one parameter. 

Any idea how to do that? I tried bridge table but had another relationship issues.

[RafalDA]

Is there any other way?

I was trying to create like a bridge table listing all possible values/combinations and then connect it somehow to main table but this failed as well.
If not, I will have to rebuild it although i'd prefer to have all fields available even if they do not havey any data that could be displayed for them. 🙂

[SamWiseOwl]

You could create the bridge tables then in the query editor do cross joins to each.

That would create a table with every possible combination.

Then create a column combining them all in to a key.

Create a new column that combines them in your main table also.

Join the two using the key.

[SamWiseOwl]

Hi @RafalDA 

The reason it doesn't work with the seperate fields:
Unit has 4 values

Status has 4 values

PBI has no idea how these 2 related, can a unit have all 4 status or just 2.

If you instead create your parameter table using the Main tables fields then it knows how they connect.

Here for example Status Shift has only one Shift B so it can display.