Forum Discussion

HeideDeLange's avatar
HeideDeLange
Regular Visitor
1 year ago
Solved

Data validation - dependency hierarchy without creating Data lists

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:

BrokerageBranch
Broker ABranch 1
Broker BBranch 2
Broker BBranch 3
Broker CBranch 4
Broker CBranch 5
Broker CBranch 6
Broker CBranch 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. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

    • HeideDeLange's avatar
      HeideDeLange
      Regular Visitor
      1 year ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.