Slicer like field parameters for Report PBI Server

[Smitaa]

Hello,

I want to create a slicer of multiple columns based on another slicer having parameters.

for example raw data is like this:

Event	Student ID	Maths teacher	Science Teacher	English Teacher
Exhibition	1	Ravi Chandra	Spriha Das	Vandana p
Exhibition	2	RK Das		Mamta Chobey
Exhibition	3		Rajesh Ranjan	Rajesh Ranjan

So first slicer should have values like Maths, Science and English:

Slicer 1

Maths

Science

English

And second slicer should filter based on Slicer 1 for example if Maths is selected in slicer 1 , then slicer 2 should have following values:

Slicer 2

Ravi Chandra

RK Das

 And table should show this:

Event	Student ID	Maths teacher
Exhibition	1	Ravi Chandra
Exhibition	2	RK Das

I tried with field parameters but it cant be used in report server

Please help me with this.

Thanks

[Anonymous · Accepted solution]

Hi @Smitaa ,

I think your data model is not suitable to achieve your goal. Here I suggest you to select Maths Teacher/English Teacher/Science Teacher columns and use "Unpivot Columns" function. Rename new columns and remove blank value in Teahcer Name.

Then create two dimtables for slicers.

DimSubject = 
DATATABLE (
    "Subject", STRING,
    "Teacher Category", STRING,
    {
        { "Maths", "Maths Teacher" },
        { "English", "English Teacher" },
        { "Science", "Science Teacher" }
    }
)

Dim Teacher Name = VALUES('Table'[Teacher Name])

Relationship:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.