Forum Discussion

augustindelaf's avatar
augustindelaf
Impactful Individual
9 years ago
Solved

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";...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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 Yu

  • augustindelaf's avatar
    augustindelaf
    Impactful Individual
    9 years ago

    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/;

     

    "

    ---