Dependent/independent dropdowns

Gnagno · 07-29-2020 06:06 AM

Hi all,
I have a table with Region, Country and Account. Would like to create Dependent/independent dropdowns for each in a way that:

I have the 3 of them always visible (with a standard dependency the second dropdown would become visible only when I select an element from the first but this is not the desired behavior)
I can select any possible combination of them (For example select only the Country, or a combination of Region + Account)
Depending on each dropdown selection, have the other two limit the values displayed based on the value selected (for example if I do not select any Region from dropdown1, but I select a Country from dropdown2, I want to get the Account values in dropdown3 and also the Region values in dropdown1 be limited based on the country selection) .
Any way I can achieve that?
Thanks in advance for your help

LeventK

Steve

You’ll need to use Suggested values for this with custom SELECT() expressions.

Gnagno

@LeventK Thanks I’ve seen that sample but it doesn’t make the dropdowns independent as I want.
In the sample, only when I select the lead Region I’m prompted with the Lead Country dropdown.
I want all the 3 dropdowns to be visible concurrently so that I can make a selection just from one of them or a combination of them. Based on the selection/s the other dropdowns should then limit their values.
Example: If I only select an Account, this one can be present in multiple countries and multiple regions.
I may want to select an account no matter of country/region, or I may want to select an Account from 1 country only. That’s why I’d need dropdowns that are dependent (because values would be displayed based on other dropdown selections) but also independent (because they should all be visible at the same time).

LeventK

@Gnagno
I got your point. Then as @Steve already pointed out, you need to use Suggested Values property instead of Valid_if and construct your expressions enclosed with either an IF() or an IFS() statement i.e.

IFS(
    ISNOTBLANK([Dropdown-1]),
    SELECT(
        Tablename[ColumnName],[Dropdown-1] = [_THISROW].[Dropdown-1]
    ),
    TRUE,Tablename[ColumnName]
)

Gnagno

@Steve and @LeventK
Guys you are priceless!!
Your solution works perfectly.
You’re the best!

LeventK

You’re very welcome @Gnagno.

Gnagno

@LeventK Need to abuse your ability writing expressions…
The proposed solution works very well going “downward”, meaning if I make no selection in DP1, then DP2 will be complete, while if I make a selection in DP1, DP2 will only display the matching elements. This is perfect.
It doesn’t work going “upwards”, and if I make my initial selection in DP2, DP1 still displays the full list of elements. I tried entering the same expression in DP1, referring to DP2 but it makes no difference.
There’s also a additional consideration that would make the expression more complex (thus needing your skills) and that is the third DP3 which must also come into play and make elements visible based on what I eventually have selected in DP1 and/or DP2.
Hope it all makes sense and your ears are not bleeding after all this explanation

Gnagno

@LeventK I do apologize… my mistake the expression works fine even going “upwards”.
The only lasting problem is to make the expression valid for 3 dropdowns and not just 2.

Gnagno

@Steve and @LeventK
I came up with this expression that I need to repeat in every dropdown with the correct references the other two.
If your remember, MyTable has 3 columns: Region, Country and Account.

So for example, the expression for the Country dropdown would be:

IF(
  ISNOTBLANK([Region]), 
  IF (
    ISNOTBLANK([Account]), 
    SELECT( MyTable[Country], AND ([Account] = [_THISROW].[Account], [Region] = [_THISROW].[Region] ) ) ,
    SELECT( MyTable[Country], [Region] = [_THISROW].[Region] )
     ),
  IF (
    ISNOTBLANK([Account]),
    SELECT( MyTable[Country], [Account] = [_THISROW].[Account] ),
    MyTable[Country] 
     )
  )

I am not sure this is the best way of coding the expression, so instead of moving on with an “if it works, it is correct!” I’d like to get your experts opinion.
Thanks.

Steve

It looks like your expression would work fine.