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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
reymalave
Helper I
Helper I

Conditional Measure for Conditional Formatting

Hi Team,

 

I am building the following map

If you select "Importaciones" the map shows circles with the colors "red" or "Blue" formatted as wether the YoY was negative or positive respectively. But if you select "Exportaciones" then there is no color format.

I am trying to build a measure to select the corresponding YoY calculation for "Importaciones" or "Exportaciones" but I am getting an error.

I am trying to use the following expression:

If Y/Y =
VAR AAnteriorE =
	CALCULATE(
		SUM('Exportaciones'[FOB_US_DUSLEG]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)

VAR AAnteriorI =
	CALCULATE(
		SUM('Importaciones'[CIF_US]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)

IF('Import/Export'[Import/Export])="Exportaciones";                                                                                                                                                                                        
		       
   	RETURN(
	 	DIVIDE(SUM('Exportaciones'[FOB_US_DUSLEG]) - AAnteriorE; AAnteriorE);                                                  
        )
	RETURN(
		DIVIDE(SUM('importaciones'[CIF_US])- AAnteriorI	; AAnteriorI);
		
	)                                                                                                                         
)   

But I keep getting syntax error with the IF expression. I have also used 

If Y/Y = VAR AAnteriorE =
	CALCULATE(
		SUM('Exportaciones'[FOB_US_DUSLEG]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)

VAR AAnteriorI =
	CALCULATE(
		SUM('Importaciones'[CIF_US]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)

IF(selectedvalue('Import/Export'[Import/Export])="Exportaciones");                                                                                                                                                                                        
		       
   	RETURN(
	 	DIVIDE(SUM('Exportaciones'[FOB_US_DUSLEG]) - AAnteriorE; AAnteriorE);                                                  )
	RETURN(
		DIVIDE(SUM('importaciones'[CIF_US])- AAnteriorI	; AAnteriorI);
		
	)                                                                                                                         )   

But still get syntax error.

Can you guys help me out here?

 

Thanks,

 

Reynaldo

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

It helps if you tell us what the syntax error says.  Looking at your code, I noticed a few issues.  Here's my attempt at fixing your first expression:

If Y/Y =
VAR AAnteriorE =
	CALCULATE(
		SUM('Exportaciones'[FOB_US_DUSLEG]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)
VAR AAnteriorI = CALCULATE( SUM('Importaciones'[CIF_US]); DATEADD('Calendar'[Date]; -1; YEAR) )
RETURN IF(SELECTEDVALUE('Import/Export'[Import/Export]) = "Exportaciones";
DIVIDE(SUM('Exportaciones'[FOB_US_DUSLEG]) - AAnteriorE; AAnteriorE);
DIVIDE(SUM('importaciones'[CIF_US]) - AAnteriorI; AAnteriorI); )

Let me know if you get expected results with that measure.

View solution in original post

2 REPLIES 2
Cmcmahan
Resident Rockstar
Resident Rockstar

It helps if you tell us what the syntax error says.  Looking at your code, I noticed a few issues.  Here's my attempt at fixing your first expression:

If Y/Y =
VAR AAnteriorE =
	CALCULATE(
		SUM('Exportaciones'[FOB_US_DUSLEG]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)
VAR AAnteriorI = CALCULATE( SUM('Importaciones'[CIF_US]); DATEADD('Calendar'[Date]; -1; YEAR) )
RETURN IF(SELECTEDVALUE('Import/Export'[Import/Export]) = "Exportaciones";
DIVIDE(SUM('Exportaciones'[FOB_US_DUSLEG]) - AAnteriorE; AAnteriorE);
DIVIDE(SUM('importaciones'[CIF_US]) - AAnteriorI; AAnteriorI); )

Let me know if you get expected results with that measure.

The error is as follows: 

The syntax for 'IF' is incorrect. (DAX(VAR AAnteriorE = CALCULATE( SUM('Exportaciones'[FOB_US_DUSLEG]), DATEADD('Calendar'[Date], -1, YEAR) )VAR AAnteriorI = CALCULATE( SUM('Importaciones'[CIF_US]), DATEADD('Calendar'[Date], -1, YEAR) )IF('Import/Export'[Import/Export])="Exportaciones", RETURN( DIVIDE(SUM('Exportaciones'[FOB_US_DUSLEG]) - AAnteriorE, AAnteriorE), ) RETURN( DIVIDE(SUM('importaciones'[CIF_US])- AAnteriorI , AAnteriorI), ) ) )).

 

But after a minor fix on your dax I was able to get it up and runnig. 

If Y/Y = 
VAR AAnteriorE =
	CALCULATE(
		SUM('Exportaciones'[FOB_US_DUSLEG]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)
VAR AAnteriorI =
	CALCULATE(
		SUM('Importaciones'[CIF_US]);
		DATEADD('Calendar'[Date]; -1; YEAR)
	)
RETURN

IF(SELECTEDVALUE('Import/Export'[Import/Export]) = "Exportaciones";
    DIVIDE(SUM('Exportaciones'[FOB_US_DUSLEG]) - AAnteriorE; AAnteriorE); 
    DIVIDE(SUM('importaciones'[CIF_US]) - AAnteriorI; AAnteriorI)
)                                                         
  

Thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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