Filter on multiple words in different rows

[RickPowerBI]

Hi all,

I want to filter on multiple words in different rows.

I succeeded to filter on one word but I can’t seem to add more, how do I do this?

This is the filter that I have working on one word:

Measure 1=

CALCULATE (

SUM( 'Analytical Report'[Column 2]),
FILTER('Analytical Report',
 FIND("Taxes", 'Analytical Report'[Column 1]), , 0) <> 0 ))

But now I need a filter on multiple words, how does this work?

(example data):

Table name: Analytical Report

Column 1	Column 2
Network service	50
Taxes	39
Property	200
EB	309
Debit	453
ODE	3456
Credit	239

I need the sum of:
- EB
-ODE
-Network service

Thanks in advance!

[harshnathani]

Hi @RickPowerBI ,

You cna try this measure.

SUM of Col1 =

SWITCH(

TRUE(),

MAX('Table'[Column 1]) = "Taxes" , CALCULATE(SUM( 'Table'[Column 2])),

MAX( 'Table'[Column 1]) = "EB" || MAX('Table'[Column 1]) = "Debit" || MAX('Table'[Column 1]) = "ODE", CALCULATE(SUM('Table'[Column 2]),FILTER(ALL('Table'),'Table'[Column 1] = "EB" || 'Table'[Column 1] = "Debit" || 'Table'[Column 1] = "ODE" ))

)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[nandukrishnavs]

@RickPowerBI 

Create DAX measure

Measure 1 =
SUMX (
    FILTER (
        'Analytical Report',
        CONTAINSSTRING (
            "EB",
            [Column 1]
        )
            || CONTAINSSTRING (
                "ODE",
                [Column 1]
            )
            || CONTAINSSTRING (
                "Network service",
                [Column 1]
            )
    ),
    [Column 2]
)

If your column 1 contains exact same text, then you can use the below measure.

Measure 2 =
SUMX (
    FILTER (
        'Analytical Report',
        'Analytical Report'[Column 1]
            IN {
            "EB",
            "ODE",
            "Network service"
        }
    ),
    [Column 2]
)

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[RickPowerBI]

Hi all,

Thank you for your suggestions but I still get blanks.

I’ve added a more detailed example of the data. The words I need to filter on are only a part of the cell. Splitting by delimiter is not an option. Can you help me fix this?

Table name: Analytical Report

Column 1	Column 2
Sales Network service	50
Taxes	39
Property	200
EB-WRTS	309
Debit	453
ODE-OBR	3456
Credit	239

I need the sum of:
- EB
-ODE
-Network service

Thanks in advance!

[nandukrishnavs · Accepted solution]

@RickPowerBI 

Try this.

Measure 1 =
SUMX (
    FILTER (
        'Analytical Report',
        NOT (
            ISBLANK (
                FIND (
                    "EB",
                    [Column 1],
                    ,
                    BLANK ()
                )
            )
        )
            || NOT (
                ISBLANK (
                    FIND (
                        "ODE",
                        [Column 1],
                        ,
                        BLANK ()
                    )
                )
            )
            || NOT (
                ISBLANK (
                    FIND (
                        "Network service",
                        [Column 1],
                        ,
                        BLANK ()
                    )
                )
            )
    ),
    [Column 2]
)

It is working for me.

If you are looking for non-case sensitive approach, for example, EB is in Debit, You have to use search() instead of find()

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[RickPowerBI]

Thank you very much, this was exactly what i needed

[AlB]

Hi @RickPowerBI 

Measure 1 =
CALCULATE (
    SUM ( 'Analytical Report'[Column 2] ),
    FILTER (
        'Analytical Report',
        'Analytical Report'[Column 1] IN { "EB", "ODE", "Network service" }
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers