Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a slicer which contains a list of text fields which are available in a column of the main dataset. I have used the SEARCH function in DAX to pick up all the values from the main dataset which contains the selected text(somewhat like the "LIKE" function in SQL). It works fine, when I select a single value, however, when multiple values are selected, it starts throwing an error. What I want, is like in SQL, when 2 or more values are selected from the slicer, it UNIONS the values. Below are the screenshots for reference and the DAX used.
Here, the Tool Name slicer is used to filter data from the In_Scope_And_Additional_Tools column using the created measure Exists.
DAX for "Exists" is :
Exists = IF(SELECTEDVALUE(Query1[ToolName]) <> " ALL",
SEARCH(SELECTEDVALUE(Query1[ToolName]),SELECTEDVALUE(SF_Opportunity[In_Scope_AND_Additional_Tools]),1,-1),1)
This works well when only 1 value is selected, However, because SELECTEDVALUE function only allows 1 selection, it doesn't allows us to select 2 or more than 2. Is there a workaround for this?
Solved! Go to Solution.
@Anonymous
Try this measure to search each item in the slicer selection and return "Exist" if found else blank.
Exists =
VAR _SELECTED = ALLSELECTED(Query1[ToolName])
RETURN
IF(
ISFILTERED(Query1[ToolName]),
IF(
ISBLANK(
COUNTX(
_SELECTED,
SEARCH(Query1[ToolName],SELECTEDVALUE(SF_Opportunity[In_Scope_AND_Additional_Tools]),1,BLANK())
)
),
BLANK(),
"Exist"
),
BLANK()
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Try this measure to search each item in the slicer selection and return "Exist" if found else blank.
Exists =
VAR _SELECTED = ALLSELECTED(Query1[ToolName])
RETURN
IF(
ISFILTERED(Query1[ToolName]),
IF(
ISBLANK(
COUNTX(
_SELECTED,
SEARCH(Query1[ToolName],SELECTEDVALUE(SF_Opportunity[In_Scope_AND_Additional_Tools]),1,BLANK())
)
),
BLANK(),
"Exist"
),
BLANK()
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous , see if this can help
https://docs.microsoft.com/en-us/dax/contains-function-dax
@amitchandak : How do we pass 2 or more values? SELECTEDVALUE is only allowing me to pass 1 value at a time.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |