Interaction between dropdown in Power BI

[vjnvinod]

Hi, how to setup an interaction between dropdowns
For instance I have 2 dropdowns(which is created out of 2 different field columns). If I select a region from the first dropdown list let say Northeast, my other dropdown should also select Northeast.
Is there a way to do this?

[TimoRiikonen]

Open Format -- > Edit Interactions

See more from https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[vjnvinod]

dropdown interaction issuedropdown interaction issue

Thanks Greg, Appreciate your time on this, i have attached the screenshot, Actual problem is that if you see in the first screenshot Dropdown when i select the HQ region as Northeast, my Delivery region won't automatically change it to Northeast or Vice versa that means i have to manually check the Northeast in the Delivery region

Look at the Output required screenshot, what i want to achieve?

i want to achieve if i select any dropdown from HQ region (be in Northeast, southwest any other region) or Delivery region(be in Northeast, southwest any other region), it should automatically change and reflect the same in both.

let me know if there is way i can fix this

[v-yuezhe-msft]

@vjnvinod,

Do the above two columns come from different tables? If so, have you set proper relationship between the two tables? 

Regards,
Lydia

[vjnvinod]

Hi Lydia,

No they come from one table,  eventhough list of regions are same, but in terms of revenue, HQ region may be not equal to Delivery Region, see the below sample for more understanding. & also look at the other table which i want to achieve through one dropdown or slicer

HQRegion	DeliveryRegion	HomeOutbound	Inbound	TER_FYTD_USD
Canada	UKI	Outbound to EMEIA	Inbound from Americas	13161834
West	West	Home	Home	10046771
Canada	Canada	Home	Home	8415244
Northeast	Northeast	Home	Home	7681898
Japan	Japan	Home	Home	7304815
West	West	Home	Home	6827022
West	West	Home	Home	6356509
West	West	Home	Home	6215388
Northeast	Northeast	Home	Home	5999410
West	West	Home	Home	5836580
Oceania	APAC SL CoE	Other Asia-Pacific outbound	Inbound from other Asia-Pacific	5572889
West	West	Home	Home	5396988
West	West	Home	Home	5396988
West	West	Home	Home	5388453
Southeast	Southeast	Home	Home	5329472
Southeast	Southeast	Home	Home	5084942
Northeast	Northeast	Home	Home	5029374
Japan	Japan	Home	Home	5028924
West	West	Home	Home	5006425
West	West	Home	Home	4978620
West	West	Home	Home	4964660
West	West	Home	Home	4939293
West	West	Home	Home	4900484
Southwest	Northeast	Other Americas outbound	Inbound from other Americas	4875573

Dropdownfor Region
Category	TER_FYTD_USD
Home	$                 453,625,289
Inbound from Asia-Pacific	$                         673,523
Inbound from EMEIA	$                   33,797,206
Inbound from Japan	$                   18,690,156
Inbound from other Americas	$                     6,976,907
Inbound from SAcct-Elim	$                     -4,557,168
Grand Total	$                 509,205,914
Home	$                 453,625,289
Other Americas outbound	$                   51,136,073
Outbound to Asia-Pacific	$                   31,934,109
Outbound to EMEIA	$                 117,022,067
Outbound to Japan	$                     3,753,733
Grand Total	$                 657,471,271

[v-yuezhe-msft]

@vjnvinod,

Could you please describe the logic that you use to calculate the expected result? I note that in your original table, there are no "Inbound from SAcct-Elim" , "Outbound to Japan" values.

In addition, regarding to your original questions, please check "Edit interactions" between your slicers following the video below.
https://1drv.ms/v/s!AhsotbnGu1Nok0TMnfg5FI_ymUlW

Regards,
Lydia

[vjnvinod]

Hi Lydia,

firstly, thanks for reverting, i looked at your videos, unfortunately it doesn't helps, as it doesn't capture the logic i am using to build the dashboard in power BI & the data which you see in the table was just a sample, please ingore those elims.

Please refer the Logic i have captured in the excel sheet1 (its a 2 different pivot) & expected result is from the combination of those two. this is only for a region, so you can refer

https://www.dropbox.com/s/69om7xsw0bujip4/Global%20TMT%20Engagement%20Data%20Extract%20FY17%20P12%20...

I want to create the last view in sheet1 through Power BI, wherein if i filter for Northeast or any other region of that matter, the below expected result should appear & numbers gets changed as per the selection.

NORTHEAST
Category	TER_FYTD_USD
Home	$                   453,625,289
Inbound from Asia-Pacific	$                           673,523
Inbound from EMEIA	$                     33,797,206
Inbound from Japan	$                     18,690,156
Inbound from other Americas	$                       6,976,907
Inbound from SAcct-Elim	$                     -4,557,168
Grand Total	$                   509,205,914
Home	$                   453,625,289
Other Americas outbound	$                     51,136,073
Outbound to Asia-Pacific	$                     31,934,109
Outbound to EMEIA	$                   117,022,067
Outbound to Japan	$                       3,753,733
Grand Total	$                   657,471,271

[vjnvinod]

@v-yuezhe-msft@Greg_Deckler

Hi Guys,

any feedback or suggestion!? please find the below link where i have saved my workings in excel,with my expected results.

if its not possible, do let me know, i am counting on your feedback.

https://www.dropbox.com/s/69om7xsw0bujip4/Global%20TMT%20Engagement%20Data%20Extract%20FY17%20P12%20...

Apprecaite your help

Regards, vinod

[v-yuezhe-msft]

@vjnvinod,

Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok0vXHolWAqtNDbXz

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

Hi Lydia,

thanks for taking your time and responding back, note that the view which you have created, i already have that view in my dashboard, unfortunately this doesn't helps, as the user has to select manually the 2 dropdowns to summarise the inbound and outbound revenue for a single region

what i am looking is a  combined  (joint ) view of those 2 matrix (which is in the PBIX file you have shared)  in Power BI using just only one dropdown (Delivery and HQ region should act as one dropdown), see the below snapshot of expected result. is it possible to create a view like this?

Expected view in Power BI

[v-yuezhe-msft]

@vjnvinod,

Please check this modified PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok007gxKrCnmc4N8Q

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft

had a chance to check, this helps but i see the matrix display is still 2.

is there a way we can have a single view covering those 2 matrix/table?

thanks for your help as always.

Regards, vinod

[vjnvinod]

@v-yuezhe-msft

Also i see that you have created 3 tables, out of which 2 tables are created (duplicated) to set the relation between HQ and Delivery.

The sample i have shared with you doesn't have many coloumns, note that the actual data set has 100+ coloumns, that means if we duplicate and set a relationship, i was wondering whether it will have performance issue or not.

Regards, vinod

[v-yuezhe-msft · Accepted solution]

@vjnvinod,

You can use DAX instead to create the new tables.  I haven't found any method to put all the data in a single Matrix visual and filter it using a single slicer.

https://1drv.ms/u/s!AhsotbnGu1Nok0_5dfG96lEqpQr_

Regards,
Lydia

[vjnvinod]

@v-yuezhe-msft@Greg_Deckler

thanks Lydia, appreciate your feedback.!

is that something we can submit as an idea to implement in Power BI?

at the moment i can use the option you have shared, but i still see challenges regarding the position of the matrix. if you see with the selection of the region, this matrix position keeps moving its position and don't fit appropriately and makes it looks weird. i have applied the lock option in the power BI but still it does really helps.

may be if you have solution for this, do let me know

[vjnvinod]

@v-yuezhe-msft

Hi Lydia,

Can you please help checking when i select a region, my outbound details (lets say filter of Northeast region) are not interacting with my Pie chart and the table.

please find below the scrubbed PBIX file

https://www.dropbox.com/s/20u6v9kpwr6xehc/Testcheck.pbix?dl=0

thanks for your help as always

[HS_PDS]

@vjnvinod 

Were you ever able to find a solution or workaround? We also have a similar issue regarding auto-populating filters and are interested.