Power BI cascading dropdowns

Power BI
1

Hi there,

First I want to say that I saw the two Power Apps solutions for cascading dropdowns, but I would like to know how to do this in plain old Power BI and without resorting to bi-directional relationships or a drastic model change.

So, in simple terms, I would like my dropdowns to filter each other so the user doesn’t have to wade through lots of unrelated options. For example: in my attached file you’ll see that if a Department is selected, the other dropdown lists do not adjust their lists to only include relevant Agencies and Managers, and vice versa. So, if the user selects Department25, the Agency dropdown should only have Agency49 in its list and the Manager dropdown list should only have Manager11. If the user selects Department21, the Agency dropdown should only have Agency25, Agency43, and Agency45 in its list and the Manager dropdown list should only have Manager30, Manager49, and Manager50. And so forth… Also, assume the dropdown lists are from different lookup tables.

Hope you get the idea and can help with this one!

Cascade Dropdowns.zip (189.1 KB)

2

Hi @powerbideveloper

Create a measure and use that measure is not blank in filters on this visual(Slicers).

cascading dropdowns

1 Like
3

@powerbideveloper ,

There are two different ways to do this:

  1. The first is DAX-based, using visual-level filters on your slicers. The article below explains how to do this.
  1. A far simpler way to handle this is with a “junk dimension”. Since the fields that would make up the junk dimension are higher cardinality than is typical for junk dimension attributes, I would suggest limiting the attribute combinations only to those occuring in the fact table, rather than doing a full crossjoin.

While simpler, this does not meet the requirement if you are unable/unwilling to combine attributes currently from different lookup tables into one. If that’s a hard and fast requirment, then you will definitely need to go with option 1. Below is a video I did last week on the use of junk dimensions for this type of purpose.

I hope this is helpful. Solution file implementing option #2 attached below.