Forum Discussion
Count with string search
Hello,
I have two different sample tables and need two different measures for different use cases. I want to count the orders distinct which has the string W3W, B2B, B2C. On Table1 the String stands for itself, on Table2 the string is wrapped up inside other letters or numbers. Sometimes there are two numbers or letters at the front or at the end, so its highly irregular:
Table1
| Ordernumber | String |
| 1234 | W3W |
| 1235 | B2B |
| 1236 | B2C |
| 1237 | SLS |
| 1238 | WAS |
| 1239 | TAS |
| 1240 | MAS |
| 1241 | LAS |
Table2:
| Ordernumber | String |
| 1234 | ZZW3WZZ |
| 1235 | ZB2BZ |
| 1236 | ZB2CZ |
| 1237 | BSLSB |
| 1238 | MWASM |
| 1239 | 8TAS8 |
| 1240 | 9MAS9 |
| 1241 | 3LAS3 |
And also if I needed to count base on two columns string search:
Here I want for example searching for W3W , B2B and B2C but only when in column string2 its already CLSD (closed)
Table3:
| Ordernumber | String | String2 |
| 1234 | W3W | OPEN DLVR |
| 1235 | B2B | BACK CLSD |
| 1236 | B2C | Open |
| 1237 | SLS | END CLSD |
| 1238 | WAS | OPEN DLVR |
| 1239 | TAS | BACK CLSD |
| 1240 | MAS | Open |
| 1241 | B2C | END CLSD |
Thank you very much in advance.
Best.
Flag_Measure = NOT ISEMPTY( FILTER( ORDERS, ORDERS[String] IN { "W3W", "B2B", "B2C" } && CONTAINSSTRING( ORDERS[String2], "Clsd" ) ) )
5 Replies
Power Query works better than DAX in your case,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUQo3DgeS/gGufgouPmFBSrE6YClToKCTkROIdHT2VnD2CXaBSZmBpZxBugpS8xQUYOLmQJFgn2Ag6erngqLDAmSPYzA2eyyBgiFgKXR7TAyAgr4QXcj2mBjC7UfYEwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ordernumber = _t, String = _t, String2 = _t]), #"Added Custom" = Table.AddColumn(Source, "Flag", each Text.Contains([String2], "CLSD", Comparer.OrdinalIgnoreCase) and List.Contains({"W3W", "B2B", "B2C"}, [String])) in #"Added Custom"CNENFRNL thank you. I still hope there is a better way in DAX to solve this. It is a big disadvantage if it cannot do "simple" wildcards search, which is such a essential part of a BI-Software. It's a big mess that in many circumstances, we need to calculate another Flag for search.
Flag_CC = ORDERS[String] IN { "W3W", "B2B", "B2C" } && CONTAINSSTRING( ORDERS[String2], "Clsd" )
