cancel
Showing results for
Did you mean:
Highlighted
Helper III

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

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.

Alena

7 REPLIES 7
Highlighted
Super User V

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

Hi @alenashkel ,

Can you give some data as example ?

Ricardo

Proud to be a Super User!

Highlighted
Super User VII

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Helper III

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

@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

Highlighted
Super User V

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

Hi @alenashkel ,

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

Proud to be a Super User!

Highlighted
Helper V

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

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

Highlighted
Helper III

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

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

Highlighted
Helper V

## Re: Measure with several restrictions, 'ANDS' and 'ORS'

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