Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello -
I wonder if anyone can spot the problem with my dax query?
I am trying to search within a text using multiple parameters - The parameters are "Text search 1" ,"Text Search 2" and "Text Search 3" and the text i am trying to search is "Casenote text"
The formula is supposed to provide a disrinct count of record ID if any one of the 3 searches is found within the text.
I tried a few different formulas - here is my latest attempt using search and ||.
the error it is throwing up is "Cannot convert "text" value of type text to True/False?
I also want to do something simliiar with And as the operator...
Would be grateful if anyone could suggest where i am going wrong?
Solved! Go to Solution.
Hi, @Anonymous
You may try the following measure to see if it helps.
Casenote mention count =
VAR s1 =
MAX ( 'Text Search'[Text Search] )
VAR s2 =
MAX ( 'Text Search 2'[Text Search 2] )
VAR s3 =
MAX ( 'Text Search 3'[Text Search 3] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'All Casenotes'[CN Record ID] ),
FILTER (
'All Casenotes',
CONTAINSSTRINGEXACT ( [Casenote text], s1 )
|| CONTAINSSTRINGEXACT ( [Casenote text], s2 )
|| CONTAINSSTRINGEXACT ( [Casenote text], s3 )
)
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thansk very much both, seems like I need to learn more about the use of variables.
Hi, @Anonymous
You may try the following measure to see if it helps.
Casenote mention count =
VAR s1 =
MAX ( 'Text Search'[Text Search] )
VAR s2 =
MAX ( 'Text Search 2'[Text Search 2] )
VAR s3 =
MAX ( 'Text Search 3'[Text Search 3] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'All Casenotes'[CN Record ID] ),
FILTER (
'All Casenotes',
CONTAINSSTRINGEXACT ( [Casenote text], s1 )
|| CONTAINSSTRINGEXACT ( [Casenote text], s2 )
|| CONTAINSSTRINGEXACT ( [Casenote text], s3 )
)
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous,
Try this measure:
Casenote mention count =
VAR vCasenote =
MAX ( 'All Casenotes'[Casenote text] )
VAR vText1 =
SUMX (
'Text Search',
SEARCH ( vCasenote, 'Text Search'[Text Search], 1, 0 )
)
VAR vText2 =
SUMX (
'Text Search 2',
SEARCH ( vCasenote, 'Text Search 2'[Text Search 2], 1, 0 )
)
VAR vText3 =
SUMX (
'Text Search 3',
SEARCH ( vCasenote, 'Text Search 3'[Text Search 3], 1, 0 )
)
VAR vResult =
IF (
vText1 > 0
|| vText2 > 0
|| vText3 > 0,
DISTINCTCOUNT ( 'All Casenotes'[CN Record ID] )
)
RETURN
vResult
For the AND version, replace "||" with "&&".
Proud to be a Super User!
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |