Updating one visual with the filtered data from another visual

[ittakestwo11]

Hello all,

I am new to Power BI and I have learnt a lot so far. But I have been trying to do this for a few weeks now and cannot seem to be able to pull it off. So I have data which has the following columns where SN is the unique serial number for a product.

I have two separate visuals (Bar charts), one of which will show the SN according to the 'Country' and the other one will show the SN according to 'Material'. Now what I want to do is to have two slicers to slice these visuals (explanation follows):

1) Category List slicer: This is just a slicer which shows two options to select 'Country' and 'Material'.

2) Top N slicer: This slicer should affect the visuals such that only the Top N% of the SNs should be shown.

For example: If the user selects 'Material' from the 'Category List' slicer and say, 20 from the 'Top N' slicer, I first need the 'Material' visual to only show the materials which are associated with the Top 20% of the SN count. Simultaneously, I also want the 'Country' visual to now show only the countries and the SN counts associated with filtered materials from the 'Material' visual. 

So far I have both the slicers working fine BUT only one visual gets updated. So in the above example, only the 'Material' visual gets updated. However, if I manually click on each of the bars from the 'Material' visual THEN the 'Country' visual gets updated as I want to. SO is there a way to updated the 'Country' visual (and possibly other visuals in the future) based on the filtered data from the 'Material' visual. 

I hope that makes sense. Let me know if you need any clarifications. Any suggestions are appreciated! Thank you!

[Anonymous]

Hi @ittakestwo11 ，

In order to provide you with a suitable solution, could you please share the following information? Thank you.
1. Are category, product, country , material and the fields placed under the Values option of bar charts from the same table? If they are from different tables, and if some relationships are created between the tables, please share the relationship information.

2. How are the two bar charts set up (Fields pane settings)

3. If there are measures referenced in those two bar chart, please share the formula for them

4. Please share some special examples to explain your final expected result

It is better if you can share a simplified pbix file. You can refer the following thread to do it.

How to upload PBI in Community

Best Regards

[ittakestwo11]

Hello @Anonymous 

Thanks for your response. Here are answers to your questions:

1. Product, Country and Material are in the same table, but I manually created Category so it is in another table and there is no relationship between the tables as of now.

Please refer to the simplified pbix file that I have shared with you via a private message (let me know if you have not received it) which will hopefully answer your questions 2 and 3. Do reach out to me if you need further clarification regarding anything in the PowerBI file.

I finally want there to be interaction between the two (and more in the future) bar charts. For eg., if I select 'Material' in 'Category List' and 5% in 'Select Top N', in addition to the 'Material' bar chart showing only Top 5% materials, I also need the 'Country' bar chart to only show the countries associated with thos top 5% materials. The image below shows the current output that I have where the 'Country' chart does not get updated. 

BUT if I hold Ctrl and select each of the bars in the 'Material' chart, I get the expected output! I just need this to happen without having to manually select each of the bars in one chart.

[Anonymous · Accepted solution]

Hi @ittakestwo11 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Update the formula of measure [CountryRank] and [MaterialRank] as below

CountryRank = 
RANKX (
    ALL ( 'Initial Customer (ex works)'[Country] ),
    CALCULATE (
        [SUMMARY],
        ALLEXCEPT (
            'Initial Customer (ex works)',
            'Initial Customer (ex works)'[Country]
        )
    )
)

Material Rank = 
RANKX (
    ALL ( 'Initial Customer (ex works)'[Material] ),
    CALCULATE (
        [SUMMARY],
        ALLEXCEPT (
            'Initial Customer (ex works)',
            'Initial Customer (ex works)'[Material]
        )
    )
)

2. Create two measures as below to get the count of SN base on the country and matrial selections

CountofSN_C = 
VAR _selcat =
    SELECTEDVALUE ( 'Select Category'[Category List] )
VAR _selcountry =
    SELECTEDVALUE ( 'Initial Customer (ex works)'[Country] )
VAR _materials =
    CALCULATETABLE (
        VALUES ( 'Initial Customer (ex works)'[Material] ),
        FILTER (
            ALLSELECTED ( 'Initial Customer (ex works)'[Material] ),
            [Material Rank] <= [Selected N]
        )
    )
VAR _countformaterial =
    CALCULATE (
        COUNT ( 'Initial Customer (ex works)'[SN] ),
        FILTER (
            'Initial Customer (ex works)',
            'Initial Customer (ex works)'[Country] = _selcountry
                && 'Initial Customer (ex works)'[Material] IN _materials
        )
    )
RETURN
    SWITCH ( _selcat, "Country", [TestMeasure], "Material", _countformaterial )

CountofSN_M = 
VAR _selcat =
    SELECTEDVALUE ( 'Select Category'[Category List] )
VAR _selmatrial =
    SELECTEDVALUE ( 'Initial Customer (ex works)'[Material] )
VAR _coutries =
    CALCULATETABLE (
        VALUES ( 'Initial Customer (ex works)'[Country] ),
        FILTER (
            ALLSELECTED ( 'Initial Customer (ex works)'[Country] ),
            [CountryRank] <= [Selected N]
        )
    )
VAR _countforcountry =
    CALCULATE (
        COUNT ( 'Initial Customer (ex works)'[SN] ),
        FILTER (
            'Initial Customer (ex works)',
            'Initial Customer (ex works)'[Material] = _selmatrial
                && 'Initial Customer (ex works)'[Country] IN _coutries
        )
    )
RETURN
    SWITCH ( _selcat, "Country", _countforcountry, "Material", [TestMeasure] )

3. Replace the Values field in the bar chart with the new measure just as below screenshot

Best Regards

[ittakestwo11]

Thank you, @Anonymous ! I have made some minor tweaks to your solution and it is giving me the output that I want! You have been very helpful. Thank you!