Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I got a requirement where I am searching for the existence of number or not in a text and return true if numbers exist or false if don't exist
ID NumbersExist
abcdef False
abc123 True
123456 True
adcf2tr True
efghijk False
Not sure if there is a function available readily.
Solved! Go to Solution.
This would be easier to do in the query editor, but you asked for DAX. Here is a column expression that gets your result. Replace T2 with your actual table name.
Has Number =
VAR vNumbers = {
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9"
}
VAR vFiltered =
FILTER (
vNumbers,
SEARCH (
[Value],
T2[ID],
,
0
) > 0
)
RETURN
IF (
COUNTROWS ( vFiltered ) > 0,
"Y",
"N"
)
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for your help and the solution you have provided did worked.
Has Number =
VAR vNumbers = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" }
VAR vFiltered =
FILTER (
vNumbers,
SEARCH ( [Value], 'TableName'[ColumnName],, 0 ) > 0
)
RETURN
IF ( COUNTROWS ( vFiltered ) > 0, "Y", "N" )
I have tried the approach and it's not working
found this long way
Did you try it as a calculated column? Did you replace T2[ID] with 'User Security'[Principal Name]?
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
If you decide to do in the query editor, you can use this in a custom column
= not List.IsEmpty(List.Intersect({Text.ToList([ID]), {"0".."9"}}))
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This would be easier to do in the query editor, but you asked for DAX. Here is a column expression that gets your result. Replace T2 with your actual table name.
Has Number =
VAR vNumbers = {
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9"
}
VAR vFiltered =
FILTER (
vNumbers,
SEARCH (
[Value],
T2[ID],
,
0
) > 0
)
RETURN
IF (
COUNTROWS ( vFiltered ) > 0,
"Y",
"N"
)
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
HI @mahoneypat
thanks for the response. it works properly.
what about the other way arround? instead of numbers, letters?
i will refer to my post, if you can answer:
Thanks
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |