Dynamic drop down from measure with data from one table

[Spotlamp]

I think this should be a simple problem but I've spent about 5 hours on it and still haven't solved it.

I'd like to create a dynamic drop down list based on data from a slicer.  The concept is that if you select only 1 "office" (the orange list), the blue drop down list should contain the custom meeting names for that office.  However, if you select none, or multiple offices, the blue drop down list should contain the standard meeting names.

This then needs to be able to filter the main table in white to show only the relevant values that would be selected from the blue drop down.

I managed to create a measure that counts how many offices have been selected in the orange box but I was unable to figure out how to use it to filter the main table.  When I added it as a column I didn't get the results I was expecting.  The formula for the measure that counts the items selected is: 

One Office Selected = IF(IF(ISFILTERED(Meetings[office]),COUNTROWS(VALUES(Meetings[office])),0)=1,1,0)

Any help would be greatly appreciated, I'm losing my marbles with this one.

Many thanks

H

[Spotlamp]

This would be a sample of data from the survey table - linked to the meetings table on id.  I couldn't figure out how to upload a .pbix.

idquestionresponse

1	Did the meeting start on time	1
1	Were minutes taken	1
1	Were actions created	0.5
1	Was their a call for recognition	0.5
2	Did the meeting start on time	1
2	Were minutes taken	0.5
2	Were actions created	1
2	Was their a call for recognition	0.5
3	Did the meeting start on time	0.5
3	Were minutes taken	0
3	Were actions created	0
3	Was their a call for recognition	0.5
4	Did the meeting start on time	0.5
4	Were minutes taken	1
4	Were actions created	0.5
4	Was their a call for recognition	0.5
5	Did the meeting start on time	0.5
5	Were minutes taken	1
5	Were actions created	1
5	Was their a call for recognition	0.5
6	Did the meeting start on time	1
6	Were minutes taken	0.5
6	Were actions created	0.5
6	Was their a call for recognition	1
7	Did the meeting start on time	0
7	Were minutes taken	0.5
7	Were actions created	0
7	Was their a call for recognition	0
8	Did the meeting start on time	1
8	Were minutes taken	0
8	Were actions created	1
8	Was their a call for recognition	1
9	Did the meeting start on time	0.5
9	Were minutes taken	0.5
9	Were actions created	0.5
9	Was their a call for recognition	0.5
10	Did the meeting start on time	0.5
10	Were minutes taken	1
10	Were actions created	0.5
10	Was their a call for recognition	0
11	Did the meeting start on time	1
11	Were minutes taken	0.5
11	Were actions created	0.5
11	Was their a call for recognition	0.5
12	Did the meeting start on time	0
12	Were minutes taken	0
12	Were actions created	1
12	Was their a call for recognition	0.5
13	Did the meeting start on time	0
13	Were minutes taken	0
13	Were actions created	1
13	Was their a call for recognition	0.5
14	Did the meeting start on time	1
14	Were minutes taken	0.5
14	Were actions created	0.5
14	Was their a call for recognition	1
15	Did the meeting start on time	0
15	Were minutes taken	1
15	Were actions created	1
15	Was their a call for recognition	1
16	Did the meeting start on time	1
16	Were minutes taken	0
16	Were actions created	0.5
16	Was their a call for recognition	0
17	Did the meeting start on time	0.5
17	Were minutes taken	0.5
17	Were actions created	1
17	Was their a call for recognition	0.5
18	Did the meeting start on time	0.5
18	Were minutes taken	0.5
18	Were actions created	0
18	Was their a call for recognition	1
19	Did the meeting start on time	0.5
19	Were minutes taken	0
19	Were actions created	0.5
19	Was their a call for recognition	0.5
20	Did the meeting start on time	0.5
20	Were minutes taken	0.5
20	Were actions created	0
20	Was their a call for recognition	0.5

[Spotlamp]

Thank you @OwenAuger, the solution works perfectly!  I haven't used parameter fields before so will do some research and learning.

The solution has enabled me to create the custom drop down which is what I asked for.  The follow up issue is that I had hoped I would be able to use it in a matrix as column headers, but it seems that's not possible.  The problem I've been asked to solve is to report on survey responses per meeting.  But different offices have different meeting names, but the same survey for each "standard" meeting type.  Therefore if you were the office manager, you could see your personal meeting names across the top of the matrix, but if you selected more than one office it would revert to the standard names across all offices.  Not sure if that's possible to solve, but I think this is a good compromise for now.

Many thanks

H

[OwenAuger]

Hi @Spotlamp 

Glad that worked for you 🙂

You can also use the same field parameter in matrix columns (or anywhere else in a visual that expects a column), with the same "Top N" filter applied to the relevant visual.

(This works as long as there aren't other Top N filters on the same visual. Otherwise you can use a similar method with the Preselected Slicer custom visual providing the field parameter filter).

I have attached the PBIX with Page 2 showing an example in Matrix column headers.

Does this work for you?

Regards

[Spotlamp]

Thanks @OwenAuger  - that worked perfectly.  I tried it before posting and it wouldn't let me drop the field parameter into the columns area.  Worked second time, must have used the measure and not the parameter.

Thanks for your support and guidance - I've managed to give the customer exactly what they were looking for.

Many thanks

H

[OwenAuger · Accepted solution]

Hi @Spotlamp 

One method I would suggest is to use a field parameter that contains the standard/custom columns.

You can then create a measure can determine whether standard/custom should be selected and then apply a filter based on this measure to the 2nd slicer.

I have attached a sample PBIX, and these are the steps I followed:

1. Create a field parameter containing standard/custom columns:

Meeting Name Parameter = 
{
    ( "standard", NAMEOF ( 'Meetings'[standard] ), 0),
    ( "custom", NAMEOF ( 'Meetings'[custom] ), 1)
}

2. Create a measure Meeting Name Parameter Flag which returns 1 if the currently filtered Meeting Name Parameter should be used, otherwise 0. This is based on the condition HASONEVALUE ( Meetings[office] ):

Meeting Name Parameter Flag = 
VAR CurrentParameter =
    MAX ( 'Meeting Name Parameter'[Meeting Name Parameter] ) -- can't use SELECTEDVALUE
VAR FlagBoolean =
    IF (
        HASONEVALUE ( Meetings[office] ),
        CurrentParameter = "custom",
        CurrentParameter = "standard"
    )
VAR FlagInteger =
    INT ( FlagBoolean )
RETURN
    FlagInteger

3. Place the field parameter on the 2nd slicer, set the slicer to "Show values of selected field" and Apply a Top 1 filter to 'Meeting Name Parameter'[Meeting Name Parameter]:

4. Now the 2nd slicer field changes based on whether 1 or more values are filtered on the 1st slicer:

Does something like this work for you?

Regards