Need help with Dax

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

* Column:
Flag =

VAR SO = Table2[Object]

VAR Tab = FILTER(Table1, CONTAINSSTRING(LEFT(SO,LEN(Table1[Type])),Table1[Type]))

RETURN

IF( COUNTROWS(Tab) > 0, TRUE(),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

Need Help

amitchandak
4 years ago
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

Fowmy

4 years ago

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]
    )
)

5 Replies

amitchandak
Super User
4 years ago
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
  Frequent Visitor
  4 years ago
  Hello amitchandak ,
  
  Thanks a lot, it's working perfectly. Greaful for Quick response.
  
  Regards,
  Sarath.
- SarathB2
  Frequent Visitor
  4 years ago
  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.
  - Fowmy
    Super User
    4 years ago
    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

Fowmy

Super User

4 years ago

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]
    )
)

Forum Discussion

Need help with Dax