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

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.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

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