topic Re: Filter on multiple words in different rows in DAX Commands and Tips

Filter on multiple words in different rows

RickPowerBI — Mon, 25 May 2020 12:54:18 GMT

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!

Re: Filter on multiple words in different rows

AlB — Mon, 25 May 2020 13:09:26 GMT

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

Re: Filter on multiple words in different rows

nandukrishnavs — Mon, 25 May 2020 13:18:56 GMT

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 🙂

Re: Filter on multiple words in different rows

Anonymous — Mon, 25 May 2020 13:22:15 GMT

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)

Re: Filter on multiple words in different rows

RickPowerBI — Tue, 26 May 2020 06:57:48 GMT

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!

Re: Filter on multiple words in different rows

nandukrishnavs — Tue, 26 May 2020 07:19:52 GMT

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 🙂

Re: Filter on multiple words in different rows

RickPowerBI — Tue, 26 May 2020 08:31:35 GMT

Thank you very much, this was exactly what i needed