Forum Discussion

ittakestwo11's avatar
ittakestwo11
Regular Visitor
4 years ago
Solved

Updating one visual with the filtered data from another visual

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 follo...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
ittakestwo11
Regular Visitor
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      ittakestwo11
      Regular Visitor
      4 years ago

      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!