Forum Discussion

Jayaselvan's avatar
Jayaselvan
Icon for Helper II rankHelper II
4 years ago
Solved

Filter with text contains with AND Logic

Hello All, I have a input file shown as below.   There are two filters brand, region Output1:   Output 2:   File Link: https://docs.google.com/spreadsheets/d/16MQRA2GiufoQ7q...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Jayaselvan 
    Please refer to attached sample file with the solution

    Filtered Value = 
VAR SelectdBrands = VALUES ( Brands[Brand] )
RETURN
    SUMX ( 
        VALUES ( 'Table'[brand] ),
        VAR CurrentBrands = 'Table'[brand]
        VAR String1 = SUBSTITUTE ( CurrentBrands, "[", "" )
        VAR String2 = SUBSTITUTE ( String1, "]", "" )
        VAR String3 = SUBSTITUTE ( String2, "“", "" )
        VAR String4 = SUBSTITUTE ( String3, "”", "" )
        VAR String5 = SUBSTITUTE ( String4, " ", "" )
        VAR Items = SUBSTITUTE ( String5, ",", "|" )
        VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T1 = GENERATESERIES ( 1, Length, 1 )
        VAR T2 = ADDCOLUMNS ( T1, "@Brand", PATHITEM ( Items, [Value] ) )
        VAR T3 = SELECTCOLUMNS ( T2, "@@Brand", [@Brand] )
        VAR T4 = INTERSECT ( T3, SelectdBrands )
        RETURN
            IF ( COUNTROWS ( SelectdBrands ) = COUNTROWS ( T4 ), CALCULATE ( SUM ( 'Table'[value] ) ) )
    )

     

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Jayaselvan 
Please refer to attached sample file with the solution

Filtered Value = 
VAR SelectdBrands = VALUES ( Brands[Brand] )
RETURN
    SUMX ( 
        VALUES ( 'Table'[brand] ),
        VAR CurrentBrands = 'Table'[brand]
        VAR String1 = SUBSTITUTE ( CurrentBrands, "[", "" )
        VAR String2 = SUBSTITUTE ( String1, "]", "" )
        VAR String3 = SUBSTITUTE ( String2, "“", "" )
        VAR String4 = SUBSTITUTE ( String3, "”", "" )
        VAR String5 = SUBSTITUTE ( String4, " ", "" )
        VAR Items = SUBSTITUTE ( String5, ",", "|" )
        VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
        VAR T1 = GENERATESERIES ( 1, Length, 1 )
        VAR T2 = ADDCOLUMNS ( T1, "@Brand", PATHITEM ( Items, [Value] ) )
        VAR T3 = SELECTCOLUMNS ( T2, "@@Brand", [@Brand] )
        VAR T4 = INTERSECT ( T3, SelectdBrands )
        RETURN
            IF ( COUNTROWS ( SelectdBrands ) = COUNTROWS ( T4 ), CALCULATE ( SUM ( 'Table'[value] ) ) )
    )

 

 

Jayaselvan's avatar
Jayaselvan
Icon for Helper II rankHelper II
4 years ago

Hello,

 

Thank you. That'a awesome. 

I wish to be excelled like you in DAX.

Could you please suggest any resources, YouTube videos or Courses that helped you.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Jayaselvan 
    Starting with "the definitive guide to dax" is the best way to learn how to think DAX. After that you will be able to create new things such as this one.