Forum Discussion

mng's avatar
mng
Icon for Advocate I rankAdvocate I
3 years ago
Solved

How to create a drop down list that returns a value

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 t...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

daXtreme's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
3 years ago

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's avatar
    mng
    Icon for Advocate I rankAdvocate I
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        mng
        Icon for Advocate I rankAdvocate I
        3 years ago

        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.