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/;"
---
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:
Tutorial: Create calculated columns in Power BI Desktop
Best Regards,
Qiuyun Yu
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 !
if 1st link doesn't work, I put the file on google drive :
https://drive.google.com/file/d/0B0os9aXobQDBLWJhQkM4dzg3alk/view?usp=sharing