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.
Hello Experts,
Good Day!
I am stuck with finding and extract the values from a column which "StartsWith" any of the value from another column which are true.
Example -
I have two tables with one column each
Table 1
Type |
Cat |
Dog |
Bag |
Ball |
Hello |
Table 2
Object |
Dog eye |
Cat Food |
School Bag |
BaseBall |
!HelloWorld |
I achived output to check whether Object started with Type or not like below result with below DAX queries
Output
Object | Flag |
Dog eye | TRUE |
Cat Food | TRUE |
School Bag | FALSE |
BaseBall | FALSE |
!HelloWorld | FALSE |
(or)
Flag =
IF (
SUMX (
Table1,
FIND (
UPPER ( Table1[Type] ),
LEFT ( UPPER ( Table2[Object] ), LEN ( Table1[Type] ) ),
,
0
)
) > 0,
TRUE (),
FALSE ()
)
Now I am seeking for the result like below , if Object column started with Type then i want that "Type" name in the column means where it is True and remaing blank().
Result,
Object | Flag | Type |
Dog eye | TRUE | Dog |
Cat Food | TRUE | Cat |
School Bag | FALSE | |
BaseBall | FALSE | |
!HelloWorld | FALSE |
Thanks in Advance...
Regards,
Sarath.
@gvrajesh , @Icey , @AlexisOlson , @v-easonf-msft ,@amitchandak ,@AllisonKennedy , @parry2k
Solved! Go to Solution.
@SarathB2 , Try like
Flag =
VAR SO = Table2[Object]
VAR _Tab = maxx(FILTER(Table1, CONTAINSSTRING(LEFT(SO,LEN(Table1[Type])),Table1[Type])), Table[Type])
RETURN
_tab
@SarathB2
Try the following column:
Item =
VAR SO = Table2[Object]
VAR Tab = VALUES('Table1'[Type] )
RETURN
IF(
Table2[Flag],
MAXX(
FILTER(
Table1,
CONTAINSSTRING( SO , Table1[Type] )
),
Table1[Type]
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@SarathB2
Try the following column:
Item =
VAR SO = Table2[Object]
VAR Tab = VALUES('Table1'[Type] )
RETURN
IF(
Table2[Flag],
MAXX(
FILTER(
Table1,
CONTAINSSTRING( SO , Table1[Type] )
),
Table1[Type]
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@SarathB2 , Try like
Flag =
VAR SO = Table2[Object]
VAR _Tab = maxx(FILTER(Table1, CONTAINSSTRING(LEFT(SO,LEN(Table1[Type])),Table1[Type])), Table[Type])
RETURN
_tab
Hello @amitchandak , @Fowmy
In my case , one of Type is "ANG"
when i try to search that one "ANGULAR" also consedering in Object as pass as it is staring with ANG.
how to avoid this .....
I tried to give a space after ANG like "ANG " but when its come to Desktop its not taking the space .
Please assist,
Thanks in advance.
@SarathB2
I modified my code, now you do not need the Flag column.
Item =
VAR SO = Table2[Object]
VAR Tab = VALUES('Table1'[Type] )
VAR Obj =
IF( SEARCH(" ", Table2[Object],1,BLANK()) = BLANK(),
Table2[Object],
TRIM(LEFT( Table2[Object] , SEARCH(" ", Table2[Object],1,BLANK()) ))
)
VAR Result =
MAXX( FILTER(ALL('Table1'[TYPE]), Table1[Type] = OBJ) , Table1[Type] )
RETURN
Result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @amitchandak ,
Thanks a lot, it's working perfectly. Greaful for Quick response.
Regards,
Sarath.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |