cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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.

 

Thank you for your help 

 

Alena

7 REPLIES 7
Highlighted
Super User V
Super User V

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

Hi @alenashkel ,

 

Can you give some data as example ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
Super User VII
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.

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






Did I answer your question? Mark my post as a solution.

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





Highlighted
Helper III
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):

 

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

 

Thank you for your help

Highlighted
Super User V
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Highlighted
Helper V
Helper V

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

Hi @alenashkel 

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
Helper III

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

@othy_bi Thank you for your reply 🙂

 

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

Highlighted
Helper V
Helper V

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

@alenashkel 

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors