Forum Discussion
IF formula with multiple conditions
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/;"
---
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 !
- 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
