Measure with several restrictions, 'ANDS' and 'ORS'

[Anonymous]

Hello everyone 🙂

Hope someone can help me with this one.

I need a measure that sums values from a columns depending on strings from another one. 

A simple example:

measure= CALCULATE(SUM(Table[Column]); Table[0ther Column] = "Some string")

I need this measure but with more restrictions, like:

First I need the column to have this string: "String X"

THEN 

I need the column to have these two: "String Y" AND "String Z"

I'm aware this might be a little confusing, I tried my best to explain it.

Thank you for your help 

Alena

[DataVitalizer]

Hi @Anonymous 

You have to use SUMX instead

SUMX(FILTER(your_table ,OR(Column_name="String Y",Column_name="String Z")),Column_values)

Does it work ? Mark it as a solution 

[Anonymous]

@DataVitalizer Thank you for your reply 🙂

But what about "String X" that has to come before the other two? 

[DataVitalizer]

@Anonymous 

In the OR clause you can put as much restrictions as you want

[parry2k]

@Anonymous it is surely confusing, better is to put sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[camargos88]

Hi @Anonymous ,

Can you give some data as example ?

Ricardo

[Anonymous]

@camargos88 @parry2k  Yes, of course:

What I need is the sum of the column "PageViews" when the following conditions happen. First, must appear "Banana". Then, and only after "Banana" I need to have "Apple" and "Peach

So, from the data below, the measure should return 29 (7+10+12):

PageViews	PageName
2	BananaApple
3	OrangeBanana
4	BananaPineappleApple
7	OrangeBananaApplePeach
10	BananaOrangeAppleOrangePeach
12	BananaOrangePineappleApplePeach
78	Apple
45	Banana
98	X
26	Y
87	Z

Thank you for your help

[camargos88]

Hi @Anonymous ,

Try it:

Measure = SUMX('Table';

IF(

SEARCH("Banana"; 'Table'[PageName];; 2) < SEARCH("Apple"; 'Table'[PageName];; 1) &&

SEARCH("Apple"; 'Table'[PageName];; 2) < SEARCH("Peach"; 'Table'[PageName];; 1); 'Table'[PageViews]; 0))

Ricardo