Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
VMP
Regular Visitor

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 7
VMP
Regular Visitor

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.

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 ...

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

can you share the measure please

I used same DAX function you had provided but instead of Max I used SELECTVALUE but that didn't worked so deleted.

selected value usually doesnt work in some cases prefer to use values or max 

 

eliasayyy
Super User
Super User

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
)

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors