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 ReportColumn 1Column 2Network service50Taxes39Property200EB309Debit453ODE3456Credit239 I need the sum of:- EB-ODE-Network service Thanks in advance!

RickPowerBI

Helper I

6 years ago

Solved

Filter on multiple words in different rows

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!

nandukrishnavs

6 years ago

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 🙂

6 Replies

AlB
Community Champion
6 years ago
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

nandukrishnavs

Community Champion

6 years ago

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
Helper I
6 years ago
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
  Community Champion
  6 years ago
  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 🙂

Anonymous
Not applicable
6 years ago
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)

Forum Discussion

Filter on multiple words in different rows

6 Replies

Related content

Page Level filter for different groups

Filtering Multiple Visuals with Different Filter Criteria

Multiple Years Filter

DAX FILTER with multiple criteria

Filter multiple values from different columns

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