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/16MQRA2GiufoQ7qGd1sxJK0ALPerScEsc/edit?usp=sharing&ouid=117632185103453650737&rtpof=true&sd=true

 

Please help in achieving this Output Table.

And also help me in creating the brand filter with each elements separetely like aaa, bbb, ccc.

 

 

 

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

     

     

10 Replies

  • 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.