Dynamic columns in matrix by filtering two different slicers

[AishwariyaV]

Hi All,

I am working on a HR dataset, which has users, skills, departments and their ratings data.

One user can have different ratings in different departments and in different skills.

I have hierarchy slicers which have Skills -> Ratings separately for each departments.

If i select ratings from two different departments(from two department slicers) and two different skills, it has to dyanmically be displayed in a matrix visual with selected skill and rating.

Data:

User	Dept	Skill	Rating
Anita	Department 1	A	1
Deepak	Department 1	A	2
Anita	Department 1	B	1
Deepak	Department 1	B	2
Manish	Department 1	B	3
Vivek	Department 2	c	1
Manish	Department 2	D	1
Anita	Department 2	D	2

Output :

Say, i have selected Skill A and Rating 1 (Dept Slicer 1) & Skill D and Rating 2 (Dept Slicer 2)

User name	Skill A	Skill D
Anita	1	2

Could anyone help on this

[MFelix · Accepted solution]

Hi @AishwariyaV ,

You need to create two table to make the filtering of your data I have used the following codes:

Slicer 1 = SUMMARIZE('Table','Table'[Rating],'Table'[Skill])

Slicer 2 = SUMMARIZE('Table','Table'[Rating],'Table'[Skill])

Now create the following measure:

Rating _Selected _ skills = 
IF (
     (
        SELECTEDVALUE ( 'Table'[Skill] )
            IN VALUES ( 'Slicer 1'[Skill] )
                && SELECTEDVALUE ( 'Table'[Rating] ) IN VALUES ( 'Slicer 1'[Rating] )
    )
        || (
            SELECTEDVALUE ( 'Table'[Skill] )
                IN VALUES ( 'Slicer 2'[Skill] )
                    && SELECTEDVALUE ( 'Table'[Rating] ) IN VALUES ( 'Slicer 2'[Rating] )
        ),
    MAX ( 'Table'[Rating] )
)

Now format your matrix with the following way:

• Rows: User
• Columns: Skill
• Values: Measure

Result below and in attach PBIX file: