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

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!