Forum Discussion
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!
a lady from the MS support gave me a solution that seems ok :
---
"
Hi Augustin,
Here are the measures that you will need:
- SelectedValue = IF (ISFILTERED ( Example[Indicator1] ) && HASONEVALUE ( Example[Indicator1] );LASTNONBLANK ( Example[Indicator1]; 0 );"a default value");
- SumValues = SUM(Example[Values]);
- 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/;"
---
14 Replies
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
FYI :
it must be row by row operation,
then it has to be a calculated column, and not a measure.
thank you
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
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 !
Hi augustindelaf,
Please try to create a measure like below to see if it meet your requirement:
Measure = SWITCH(TRUE(),MAX('DATA(Update KPIs)'[Work Stream ])="WS 1.1" || MAX('DATA(Update KPIs)'[Work Stream ])="WS2.1" || MAX('DATA(Update KPIs)'[Work Stream ])="WS 3.1" || MAX('DATA(Update KPIs)'[Work Stream ])="WS 3.4",SUM('DATA(Update KPIs)'[KPI 2 Monthly Actual]),
MAX('DATA(Update KPIs)'[Work Stream ])="WS 2.2" || MAX('DATA(Update KPIs)'[Work Stream ])="WS 3.5",AVERAGE('DATA(Update KPIs)'[KPI 2 Monthly Actual]))Best Regards,
Qiuyun Yua lady from the MS support gave me a solution that seems ok :
---
"
Hi Augustin,
Here are the measures that you will need:
- SelectedValue = IF (ISFILTERED ( Example[Indicator1] ) && HASONEVALUE ( Example[Indicator1] );LASTNONBLANK ( Example[Indicator1]; 0 );"a default value");
- SumValues = SUM(Example[Values]);
- 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/;"
---
I'm relatively new to PowerBI and DAX and I'm having a problem with a similar issue (not as complicated I think). I need to use the volume if it is current YTD, Actuals and ITA otherwise 0.
These are the two DAX statements I have tried:
_CurrentYearITA = IF('AMER DBP Retail Bookings'[DTF_Current_ITA_YTD] = "Y"||'AMER DBP Retail Bookings'[PL_PlanCode] = "ACTUALS"|| 'AMER DBP Retail Bookings'[CO_Company] = "ITA";'AMER DBP Retail Bookings'[_Volume];0)
and
_CurrentYearITA = IF(AND('AMER DBP Retail Bookings'[DTF_Current_ITA_YTD] = "Y",'AMER DBP Retail Bookings'[PL_PlanCode] = "ACTUALS",'AMER DBP Retail Bookings'[CO_Company] = "ITA"),'AMER DBP Retail Bookings'[_Volume],0)
The first one gives a bad syntax error starting with the semi-colon after "ITA" and the second one says too many arguments for AND function.
Any assistance would be appreciated.
Try This if u want ...
CalculatedColumn= SWITCH(
TRUE(),
TableName[ColumnName] = "A",
Value(123),
TableName[ColumnName] = "B",
Value(124),
TableName[ColumnName] = "C",
Value(125),
TableName[ColumnName] = "D",
Value(126),
TableName[ColumnName] = "E",
Value(127),
TableName[ColumnName] = "F",
Value(128),
TableName[ColumnName] = "G",
Value(129),
TableName[ColumnName] = "H",
Value(130),
TableName[ColumnName] = "I",
Value(131),
TableName[ColumnName] = "J",
Value(132),
TableName[ColumnName] = "K",
Value(134),
TableName[ColumnName]= "L",
Value(135),
TableName[ColumnName] = "M",
Value(136),
-1)
