Data validation - dependency hierarchy without creating Data lists

[HeideDeLange]

Hi all,

I need some help. I have a list of Brokerages and Branches and I am creating a form where the user chooses the Brokerage and then must choose a branch. I only want the branches belonging to the selected Brokerage to show. 

Sample data:

Brokerage	Branch
Broker A	Branch 1
Broker B	Branch 2
Broker B	Branch 3
Broker C	Branch 4
Broker C	Branch 5
Broker C	Branch 6
Broker C	Branch 7

Now all posts I have seen require you to create named ranges/lists of the branches under each brokerage. However my data contains 500+ brokerages with in total 1400+ branches, and these update weekly, which means creating lists/named ranges is not feasible. 

Is there no way to set it to essentially say:

Choose option from column "Brokerage" in A1.

In A2 only Branches that have Brokerage = A1 are displayed

Any guidance will be appreciated - It is being built for inexperienced users - so it cant need refreshes/macros etc to run inbetween steps. 

[Anonymous]

Hi @HeideDeLange 

You can use slicers to implement your needs.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HeideDeLange]

Hi there, 

Thanks, I learnt something new today, but the slicers don't work, unless you can help with the next step.

So selecting the data was one thing, then I use the result of the selection in a vlookup to bring back a data point. 

Can I reference the slicer selection in a vlookup formula?

[Ashish_Mathur]

That is an MS Excel question - not  a PowerBI question.

[Anonymous · Accepted solution]

Hi @HeideDeLange 

Of course you can! You can use the SELECTEDVALUE( ) function to get the result of the slicer selection.

Test = SELECTEDVALUE('Table'[Brokerage])

Put this measure in the card visual to validate the results:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.