Filter with text contains with AND Logic

[Jayaselvan]

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=11763...

Please help in achieving this Output Table.

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

[tamerj1 · Accepted solution]

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]

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]

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

[Jayaselvan]

Please help @amitchandak , @lbendlin , @Rayshin , @tamerj1 , @SpartaBI 

[onurbmiguel_]

Hello 

Please take a look on my solution for that 

https://1drv.ms/u/s!AkcWVrMFkXs1h7MWr8PnmrO3yP-_VA?e=x9koO4

any question please ask 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

[Jayaselvan]

Hello,

Thanks for helping me out.

But I can't access the link(https://1drv.ms/u/s!AkcWVrMFkXs1h7MWr8PnmrO3yP-_VA?e=x9koO4) which you shared.

[onurbmiguel_]

hello again, 

Can you try with different browser, i test it in chrome with incognito windows and works

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

[Jayaselvan]

Hello,

Feeling sorry to bother you again. 

Still I can't access the link even in Google Chrome Incognito as well.

This is the version I my using in my Windows 10 machine.

[onurbmiguel_]

Hi again, 

Try this link 

https://drive.google.com/file/d/19_ps__mKX0CZIteXBZnXRQ46p-sDCBBc/view?usp=sharing

BR 

Bruno 

[Jayaselvan]

Hello

Thank you, I can access the link now.

It satisfies Sample Output 1.

But the output 2(Selecting multiple brands) is not achieved here. We have to select the rows which contains all the selected value.

Your Output:

The expected Output