cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
augustindelaf Skilled Sharer
Skilled Sharer

IF formula with multiple conditions

Hi,

 

I would like to create a DAX formula with a IF statement.

my formula would be :

 

 IF('DATA'[Work Stream ] ="WS 1.1";SUM('DATA'[KPI 2 Monthly Actual]);

 IF('DATA'[Work Stream ] ="WS 2.1";SUM('DATA'[KPI 2 Monthly Actual]);

 IF('DATA'[Work Stream ] ="WS 2.2";AVERAGE('DATA'[KPI 2 Monthly Actual]);

 IF('DATA'[Work Stream ] ="WS 3.1";SUM('DATA'[KPI 2 Monthly Actual]);

 IF('DATA'[Work Stream ] ="WS 3.4";SUM('DATA'[KPI 2 Monthly Actual]);

 IF('DATA'[Work Stream ] ="WS 3.5";AVERAGE('DATA'[KPI 2 Monthly Actual])

 

Maybe it is possible with a look up Table ? (1st field : Work Stream, 2nd field : aggregation type) 

anyway, even without look up Table it can be great !

Thank you for your Quick answer!

 

1 ACCEPTED SOLUTION

Accepted Solutions
augustindelaf Skilled Sharer
Skilled Sharer

Re: IF formula with multiple conditions

@v-qiuyu-msft,

 

a lady from the MS support gave me a solution that seems ok :

 

---

"

Hi Augustin,

 

Here are the measures that you will need:

 

  1. SelectedValue = IF (ISFILTERED ( Example[Indicator1] ) && HASONEVALUE ( Example[Indicator1] );LASTNONBLANK ( Example[Indicator1]; 0 );"a default value");
  2. SumValues = SUM(Example[Values]);
  3. VarKPI2MonthlyTGTR = IF([SelectedValue]="WS"; AVERAGE(Example[Values]); [SumValues]);

 

In will need to substitute what is in orange with your dimensions.

 

And here are some interesting documentation:

 https://msdn.microsoft.com/en-us/library/ee634396.aspx;
http://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/;

 

"

---

 

 

View solution in original post

14 REPLIES 14
augustindelaf Skilled Sharer
Skilled Sharer

Re: IF formula with multiple conditions

FYI : 

 

it must be row by row operation,

 

then it has to be a calculated column, and not a measure.

thank you

anupampandey Helper III
Helper III

Re: IF formula with multiple conditions

Hi @augustindelaf,

 

Try below formula

 

Formula = IF(AND('DATA'[Work Stream ] ="WS 1.1", 'DATA'[Work Stream ] ="WS 2.1"),SUM('DATA'[KPI 2 Monthly Actual]),
IF(AND('DATA'[Work Stream ] ="WS 3.1", 'DATA'[Work Stream ] ="WS 3.4"),SUM('DATA'[KPI 2 Monthly Actual]),
IF(AND('DATA'[Work Stream ] ="WS 2.2", 'DATA'[Work Stream ] ="WS 3.5"),AVERAGE('DATA'[KPI 2 Monthly Actual]),0)))

 

Hope it work

 

Thanks,

Anupam

 

Super User III
Super User III

Re: IF formula with multiple conditions

Hi @augustindelaf,

 

I believe that the Switch function will work much better than the IF, try this.

 

 

SWITCH(
		TRUE();
		'DATA'[Work Stream ] = "WS 1.1";
				SUM('DATA'[KPI 2 Monthly Actual]);
		'DATA'[Work Stream ] ="WS 2.1";
				SUM('DATA'[KPI 2 Monthly Actual]);
		'DATA'[Work Stream ] ="WS 2.2";
				AVERAGE('DATA'[KPI 2 Monthly Actual]);
		'DATA'[Work Stream ] ="WS 3.1";
				SUM('DATA'[KPI 2 Monthly Actual]);
		'DATA'[Work Stream ] ="WS 3.4";
				SUM('DATA'[KPI 2 Monthly Actual]);
		'DATA'[Work Stream ] ="WS 3.5";
				AVERAGE('DATA'[KPI 2 Monthly Actual]);
		0)

 

You can change the final 0 by the default value you want.

 

Regards

 

MFelix


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


augustindelaf Skilled Sharer
Skilled Sharer

Re: IF formula with multiple conditions

Hi,

 

@anupampandey@MFelix, thanks for your solution.

 

I will keep the SWITCH solution, which to me is the easiest one.

just one problem : it does not act within the current filter context, but doing sums or averages without any filtering. 

 

and i have some filters applied (owner, action ID, Region...) but the results appear as if filters were not applied.

can you tell me how to do it to the current filter context?

it is a calculated column, not a measure, btw

 

thanks !

Microsoft dkay84_PowerBI
Microsoft

Re: IF formula with multiple conditions

Since you are aggregating, wouldn't you want to create it as a measure? Then you have a visual with [Workstream] as an axis or rows and you place the measure in as values
augustindelaf Skilled Sharer
Skilled Sharer

Re: IF formula with multiple conditions

Maybe I don't understand enough the difference between a Measure and a Calc Column.

 

But my question is : 

 

-how to make that sum &  average work  IN the current filter context ? 

if a measure can solve that, then I will do a measure.

 

thanks

Moderator v-qiuyu-msft
Moderator

Re: IF formula with multiple conditions

Hi @augustindelaf,

 

How did you set filters (owner, action ID, Region...)? If you use the calculated column to return results, filters will not affect the value in this column. I think you might need to create a measure which can be filtered.

 

Please share the sample table about 'DATA'[Work Stream ], 'DATA'[KPI 2 Monthly Actual], 'DATA'[KPI 2 Monthly Actual] and owner, action ID, Region. And show us which filtered results you want, so that we can try to create a measure to achieve your requirements.

 

By the way, regarding measures and calculated columns, please take a look at below:

Measures in Power BI Desktop

Tutorial: Create calculated columns in Power BI Desktop

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
augustindelaf Skilled Sharer
Skilled Sharer

Re: IF formula with multiple conditions

hI @v-qiuyu-msft,

 

Thank you for answering me and proposing me to send a sample.

I don't really know Measures and how for values to act in the current filter context.

 

link to the file is here :

https://filetea.me/n3wVarFBmlySNqeM61cTuQJrg

 

please go to the 1st Tab (Monthly), you will see filters on the Top. (blue ribbon)

according to some values I need some fields   to be in average or sums.

 

thanks !

 

 

 

 

augustindelaf Skilled Sharer
Skilled Sharer

Re: IF formula with multiple conditions

if 1st link doesn't work, I put the file on google drive :

 

https://drive.google.com/file/d/0B0os9aXobQDBLWJhQkM4dzg3alk/view?usp=sharing

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors