Forum Discussion
Advocate IHow 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 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?
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
6 Replies
daXtremeSolution Sage
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.
mngThank 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 listtrans_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" )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