Slicer selection to affect the values of another slicer from different table

[NancyJohn]

Have a scenerio where I have got 2 tables with same set of data. One is designed for Matrix visual and other for chart view.
It is having employee hierarchy based values like Level1, Level2, Level3 for different metrcs across periods. Each higher level has got its own value and it is not an aggregate of below levels.
What I require is to filter both matrix and chart using same employee slicer so that for example, level2 one value is selected it should show level2 and below hierchy values in matrix but in chart views, it should filter that exact level2 employee and not the aggregate of below levels. And likewise for all levels.

Currently it is 2 Employee slicers, I want it as single slicer.

Below are the screenshot of the data tables.

 

below is the link of file uploaded.
Slicer Test.pbix

[MFelix · Accepted solution]

Hi @NancyJohn ,

Try to add the following measures:

Avg Backlog = 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Backlog]), 'Emp Hierarchy'[Level3] = "")

Avg Closed= 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Closed]), 'Emp Hierarchy'[Level3] = "")

Avg Current= 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Current]), 'Emp Hierarchy'[Level3] = "")

Avg Open= 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Open]), 'Emp Hierarchy'[Level3] = "")

Has you can see the top charts return the correct result.

Please see PBIX file attach.

[v-achippa]

Hi @NancyJohn,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MFelix and @m4ni for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @NancyJohn,

We wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @NancyJohn,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by @MFelix resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[m4ni]

Hi @NancyJohn 

As @MFelix  stated, you need to create a single dimension table which will filter both tables.  To do that you can try the below code which unions the employee details from both tables and produces a unique table.

New Table  =

DISTINCT(

        UNION(

            SELECTCOLUMNS(Sheet1, "Emp", Sheet1[Employee], Sheet1[Level1], Sheet1[Level2], Sheet1[Level3])

            ,

            SELECTCOLUMNS(Sheet2, "Emp", Sheet2[Employee], Sheet2[Level1], Sheet2[Level2], Sheet2[Level3])

        )

)

You can then connect this to both tables for the filter.  Below is screenshot.

Of course rename for your tables...

HTH

[NancyJohn]

I have tried this. But what Im trying to resolve is the filtering of chart view. If Im selecting like below

Then matrix is filtered is desired way like below.

But I want Chart View also to be filters by single first row of 'Sasi' like:

but instead it is getting filtered as aggregation of all levels under 'Sasi' like:

[MFelix]

Hi @NancyJohn ,

Try to create a measure similar to this:

Filter = 

COUNTROWS (FILTER(ALLSELECTED(ChartView[Level3]),Table[Level3] = Blank()))

Now use this measure on the filter pane of each of the bar charts and select the options is not blank.

If this does not return the expected result can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[NancyJohn]

I have uploaded the pbix in one drive.
Slicer Test.pbix
Can you check if you can access?

[MFelix · Accepted solution]

Hi @NancyJohn ,

Try to add the following measures:

Avg Backlog = 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Backlog]), 'Emp Hierarchy'[Level3] = "")

Avg Closed= 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Closed]), 'Emp Hierarchy'[Level3] = "")

Avg Current= 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Current]), 'Emp Hierarchy'[Level3] = "")

Avg Open= 
CALCULATE(AVERAGEX('Chart View', 'Chart View'[Open]), 'Emp Hierarchy'[Level3] = "")

Has you can see the top charts return the correct result.

Please see PBIX file attach.

[NancyJohn · Accepted solution]

Thank you so much for the solution. Its working for me.

[m4ni]

Its a little difficult to see withtout the file, but I am thinking you need to join the tables at the correct granularity.  Joining on the Employee alone will give you aggregated results and you dont want that.  

Therefore create a KEY in the data table at your desired grain and use the key in your filter table.

May I suggest reference to this video which may help explain.  Creating a slicer that filters multiple columns in Power BI

In your case you need to define the correct grain for your filter.

HTH

[MFelix]

Hi @NancyJohn ,

The best option is to create a dimension table with that connect both tables to be filtered based on the employee. You can do it with unique values of combinations of the several levels or create some different dimension for each level and then combining it in a single slicer.