Forum Discussion

VMP's avatar
VMP
Frequent Visitor
3 years ago

If else DAX Measure

I was able to create a calcuated column at row level calculation, but I have to calculate as measure. Please guide me on how to create a Measure rather than column?

Below is DAX to create calculated column which I am successful. 
West =
IF('Target'[Forecast Area]="Operations",
    CALCULATE(SUMX('Allocation','Allocation'[Fraction of Sale])*SUMX('Target','Target'[Amount]),
        ALLEXCEPT('Target','Target'[PBI Index]),FILTER('Allocation','Allocation'[Sales to Regions]="West")),
         IF('Target'[Regions]="West",'Target'[Amount]*1,0)
)

7 Replies

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    3 years ago
    SWITCH(
TRUE(),
MAX('Target'[Forecast Area])="Operations", 
CALCULATE(
       SUMX('Allocation','Allocation'[Fraction of Sale])*SUMX('Target','Target'[Amount]),
       ALLEXCEPT('Target','Target'[PBI Index]),FILTER('Allocation','Allocation'[Sales to 
       Regions]="West")
),
MAX('Target'[Regions]) ="West",
'Target'[Amount]*1,
0
)
  • VMP's avatar
    VMP
    Frequent Visitor
    3 years ago

    Thanks for input! Getting belowe error message.

     

    A single value for column 'Forecast Area' in table 'Target' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

    • eliasayyy's avatar
      eliasayyy
      Memorable Member
      3 years ago

      oh my bad 

      we need to change target[amount] because we are using it in measures can you please let me know how you want it? like amount multiplies by 1 is row by row? or sum of it or the max ...

      • VMP's avatar
        VMP
        Frequent Visitor
        3 years ago

        The amount multiplies by 1 is row by row, I tried Sumx in your formula but still getting same error message.