Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everybody,
Suppose I have the following table, "Table 1", that has a Serial Code column containing a string of text. I am trying to filter this table based on whether or not the Serial Code contains particular substrings. The substrings I want to check for are listed in another table, "Table 2".
Table 1 | |
Row | Serial Code |
1 | ABC*********** |
2 | ***************** |
3 | ***DEF******** |
4 | *******XYZ**** |
5 | ***ABC***DEF*** |
6 | **************** |
7 | **************** |
8 | XYZ***ABC********* |
9 | ****************** |
10 | DEF*******XYZ***** |
Table 2 |
Substrings |
ABC |
DEF |
XYZ |
Basically the logic is: For each row in Table 1, check if the Serial Code contains any of the Substrings listed in Table 2. If yes, keep the rows. If no, filter out the rows. So the resulting table will be as below.
Result | |
Row | Serial Code |
1 | ABC*********** |
3 | ***DEF******** |
4 | *******XYZ**** |
5 | ***ABC***DEF*** |
8 | XYZ***ABC********* |
10 | DEF*******XYZ***** |
Is this possible with DAX?
Thank you for reading!
Solved! Go to Solution.
@Anonymous , You can try a new column like
New column =
var _1 =countx( filter(Table2, search(Table2[Substrings], Table[Serial Code],,0)> 0),Table2[Substrings])
return
if(not(isblank(_1)),1, blank())
@Anonymous , You can try a new column like
New column =
var _1 =countx( filter(Table2, search(Table2[Substrings], Table[Serial Code],,0)> 0),Table2[Substrings])
return
if(not(isblank(_1)),1, blank())
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |