Nested If measures (DAX)

DAX DAX Calculations
1

Hi All,

I’m trying to create calculated measure based on existing measures when they are empty or zero.

I’m trying below dax code but still having issues as in the file i have highlighted in column E where row 5 & 30 is not picking right values.Can someone help me with the dax?
Sample data.xlsx (14.4 KB)

var __measure1blank=if(or(ISBLANK(__measure1),__measure1=0),0,__measure1)

var __measure2blank=if(or(ISBLANK(__measure2),__measure2=0),0,__measure2)

var __measure3blank=if(or(ISBLANK(__measure3),__measure3=0),0,__measure3)

Return

if(__measure1=0,__measure2,if ((__measure1blank+__measure2blank)=0.00,__measure3,__measure1))

TIA.

2

Hello @Ravi85,

Thank You for posting your query onto the Forum.

Is this the result you’re looking for? Below is the screenshot provided for the reference -

image
image773×1061 81.8 KB

To achieve this result, below is the DAX Measure provided for the reference -

Calculated Measure = 
VAR _Results_For_Measure_3 = 
( ISBLANK( SELECTEDVALUE( Data[Measure1] ) ) || SELECTEDVALUE( Data[Measure1] ) = 0 ) && 
( ISBLANK( SELECTEDVALUE( Data[Measure2] ) ) || SELECTEDVALUE( Data[Measure2] ) = 0 )

VAR _Results_For_Measure_2 = 
ISBLANK( SELECTEDVALUE( Data[Measure1] ) ) || SELECTEDVALUE( Data[Measure1] ) = 0

VAR _Results_For_Measure_1 = 
NOT ISBLANK( SELECTEDVALUE( Data[Measure1] ) ) || SELECTEDVALUE( Data[Measure1] ) <> 0

RETURN
SWITCH( TRUE() , 
    _Results_For_Measure_3 , SELECTEDVALUE( Data[measure3] ) , 
    _Results_For_Measure_2 , SELECTEDVALUE( Data[measure2] ) , 
    _Results_For_Measure_1 , SELECTEDVALUE( Data[Measure1] ) , 
SELECTEDVALUE( Data[Measure1] ) )

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Nested IF Measure - Harsh.pbix (20.9 KB)

3

Thanks @Harsh ,

i also managed using nested if statements.
var __measure1blank=if(or(ISBLANK(__measure1),__measure1=0),0,__measure1)

var __measure2blank=if(or(ISBLANK(__measure2),__measure2=0),0,__measure2)

var __measure3blank=if(or(ISBLANK(__measure3),__measure3=0),0,__measure3)
return

if(__measure1blank=0,if(__measure2blank=0,__measure3blank,__measure2blank),__measure1blank)

4

Hi @Harsh ,

can i check why the totals are not displaying correct for calculated measure?

TIA.

5

Hello @Ravi85,

Yesterday, I addressed the very same issue onto the Forum. In order to fix the totals, you can write an additional measure which fixes the results for grand totals as well.

One of our EDNA expert had already created a post on it pertaining to the topic - “How To Fix Incorrect Totals”.

Below are the links of the post provided for the reference.

Thanks and Warm Regards,
Harsh