How to create a drop down list that returns a value

[mng]

Hi,

I understand how to use a splicer to create a drop list using values from a column in my table to filter that column but what I want to is create a drop list from values that are not in my table and then have it return a value that I can use to filter through multiple columns in my main table.

Drop down list options

• Trans Any
• Trans_Opr
• Trans_Sw
• Trans_Clr

Drop down list DAX logic 

• if Trans Any selected, filter column "TRANS_OPR" by fields = "Y" && "TRANS_SWG" by fields = "Y" && "TRANS_CLR" by fields = "Y" && "TRANS_CNTR" by fields = "Y"
• if Trans Opr selected, filter column "TRANS_OPR" by fields = "Y"
• if Trans Sw selected, filter column "TRANS_SWG" by fields = "Y"
• if Trans Clr selected, filter column "TRANS_CLR" by fields = "Y"

My question is: 

1. How to create this drop down list since none of the values are specified in a column. Do I need to create a seperate table and manually add them into my power BI?

2. Is there a way that the slicer - drop down list can return a value based on the user selection that I could use to write the DAX logic to filter the columns?

3. If a slicer is not the way to do this, what other option is there to filter thats user friendly?

[daXtreme]

1. How to create this drop down list since none of the values are specified in a column. Do I need to create a seperate table and manually add them into my power BI?

>> Create a disconnected table with the entries of our choosing. This table can come from variety of sources. It can also be created manually as a calculated table in DAX or in Power Query.

2. Is there a way that the slicer - drop down list can return a value based on the user selection that I could use to write the DAX logic to filter the columns?

>> Of course. A slicer can return any value, even one you can't see in the slicer's list. It's very easy. Just create a disconnected table as above and give it 2 columns. One that will hold the visible values of the slicer and the other the value you want to return/harvest to DAX. Easy like 1,2,3.

3. If a slicer is not the way to do this, what other option is there to filter thats user friendly?

>> Slicer can do all of this. And even more.

[mng]

Thank you for the suggestions, I've created a seperate table using data from a comma seperated file. The issue is I don't understand how to relate the selections in the disconnected table  to filter my main table. Any help would be appreciated. 

The slicer is currently referencing the disconnected "Table1[column1]" to show the drop down list 

• Trans Any
• Trans_Opr
• Trans_Sw
• Trans_Clr

2.
In my main table, I created a measure column that references the selection above based on the user selection from the drop down list

trans_selection = SELECTEDVALUE('Table'[Column1])

but so far the following doesn't seem to be changing anything in my main table, I tried to create a new column using a switch to filter the data 

trans_sel_col = 
SWITCH (
    TRUE(),

    [trans_selection] = "Trans Cl", Query1[TRANS_CLR] = "Y",
    [trans_selection] = "Trans Sw", Query1[TRANS_SWG] = "Y"
)

[Anonymous · Accepted solution]

Hi @mng ,

You can update the formula of measure [trans_sel_col] as below and apply the visual-level filter with the condition (trans_sel_col is 1) as below screenshot:

trans_sel_col = 
VAR trans_selection =
    SELECTEDVALUE ( 'Table'[Column1] )
VAR _opr =
    SELECTEDVALUE ( 'Query1'[TRANS_OPR] )
VAR _swg =
    SELECTEDVALUE ( 'Query1'[TRANS_SWG] )
VAR _clr =
    SELECTEDVALUE ( 'Query1'[TRANS_CLR] )
VAR _cntr =
    SELECTEDVALUE ( 'Query1'[TRANS_CNTRC] )
RETURN
    SWITCH (
        trans_selection,
        "Trans Any",
            IF ( _opr = "Y" && _swg = "Y" && _clr = "Y" && _cntr = "Y", 1, 0 ),
        "Trans Opr", IF ( _opr = "Y", 1, 0 ),
        "Trans Sw", IF ( _swg = "Y", 1, 0 ),
        "Trans Clr", IF ( _clr = "Y", 1, 0 )
    )

If the above one can't help you get the desired result, please provide some sample data in your table “Query1” (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[mng]

Thank you very much, this was pretty much the solution.

I was trying to upload a simplified pbix but I believe I need to have cloud storage access in order to share the pbix file here.

I just had a followup questions related to this solution. 

After I added the new measure (trans_sel_col), the slider defaults to "ALL" selection when nothing is selected. This causes the main table to not display anything.

Is there a way to set a default selection on the slider so that it will show all contents in the table.
I believe if the filter default can be set to read a blank() and show everything that would be ideal.

 

[Anonymous]

Hi @mng ,

You can update the formula of measure [trans_sel_col] as below and check if that is what you want. Please find the details in the attachment.

trans_sel_col = 
VAR trans_selection =
    SELECTEDVALUE ( 'Table'[Column1] )
VAR _opr =
    SELECTEDVALUE ( 'Query1'[TRANS_OPR] )
VAR _swg =
    SELECTEDVALUE ( 'Query1'[TRANS_SWG] )
VAR _clr =
    SELECTEDVALUE ( 'Query1'[TRANS_CLR] )
VAR _cntr =
    SELECTEDVALUE ( 'Query1'[TRANS_CNTRC] )
RETURN
    IF (
        NOT ( ISFILTERED ( 'Table'[Column1] ) ),
        1,
        SWITCH (
            trans_selection,
            "Trans Any",
                IF ( _opr = "Y" && _swg = "Y" && _clr = "Y" && _cntr = "Y", 1, 0 ),
            "Trans Opr", IF ( _opr = "Y", 1, 0 ),
            "Trans Sw", IF ( _swg = "Y", 1, 0 ),
            "Trans Clr", IF ( _clr = "Y", 1, 0 )
        )
    )

Best Regards

[mng]

@Anonymous  thank you very much for helping me understand power bi better