Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
DataVitalizer
Super User
Super User

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
Not applicable

@DataVitalizer Thank you for your reply 🙂

 

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

@Anonymous 

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

parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

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!



Anonymous
Not applicable

@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

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


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

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.