Multiple conditions and values in a mesaure

[Anonymous]

Hi everyone!

I'm so stuck with something. I'm trying to create a measure that has to evaluate several conditions for the final value. Please kindly see next pic.

The 2 tables that I'm trying to use are Zona-Gestión and Inflación, related with IdZonaGestion. In the page of my report I'm displaying several measures in a table visual opened by 'Zona-Gestión'[IdZonaGestion]. The measure that is not working for me, is next:
Measure =
IF(
 'Zona-Gestión'[IdZonaGestion] = "CEN-ROE" || 'Zona-Gestión'[IdZonaGestion] = "CEN-ROW";
 'Inflación'[Inflación CEAM];
 'Inflación'[Inflación Resto]
)

'Zona-Gestión'[IdZonaGestion] is a Column
'Inflación'[Inflación CEAM] and 'Inflación'[Inflación Resto] are Measures

It seems really simple but I can't make it work. The column IdZonaGestion of Zona-Gestión table, is not recognized by the measure.

What am I doing wrong?

Thanks in advance.

[v-ljerr-msft]

Hi @Anonymous,

---

'Zona-Gestión'[IdZonaGestion] is a Column
'Inflación'[Inflación CEAM] and 'Inflación'[Inflación Resto] are Measures 

Could you post the formulas for the measures mentioned above? It's better ot share a sample pbix file in this case. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

In addition, what do you mean about "The column IdZonaGestion of Zona-Gestión table, is not recognized by the measure." Could you be more precisely with it?

Regards

[Anonymous]

Hi @v-ljerr-msft. Thanks for your reply.

As requested...

Inflación CEAM =
('Inflación'[Inflación Panamá] * ('Inflación'[Ventas USD Panamá] / 'Inflación'[Ventas USD CEAM])) +
('Inflación'[Inflación Costa Rica] * ('Inflación'[Ventas USD Costa Rica] / 'Inflación'[Ventas USD CEAM])) +
('Inflación'[Inflación Honduras] * ('Inflación'[Ventas USD Honduras] / 'Inflación'[Ventas USD CEAM])) +
('Inflación'[Inflación Nicaragua] * ('Inflación'[Ventas USD Nicaragua] / 'Inflación'[Ventas USD CEAM])) +
('Inflación'[Inflación El Salvador] * ('Inflación'[Ventas USD El Salvador] / 'Inflación'[Ventas USD CEAM])) +
('Inflación'[Inflación Guatemala] * ('Inflación'[Ventas USD Guatemala] / 'Inflación'[Ventas USD CEAM]))

An the formula for one contry for each measure above included:

Inflación Panamá =
CALCULATE(
 CALCULATE(
  CALCULATE(
   SUM(
    'Inflación'[Inflacion]
   );
   FILTER(
    'País';
    'País'[IdPais] = "PAN"
   )
  );
  LASTDATE(
   'Inflación'[Fecha]
  );
  USERELATIONSHIP(
   'Inflación'[IdPais];
   'País'[IdPais]
  )
 )
) * 100

Ventas USD Panamá =
CALCULATE(
 CALCULATE(
  'Ventas'[VentasUSD];
  FILTER(
   'País';
   'País'[IdPais] = "PAN"
  )
 );
 USERELATIONSHIP(
  'Inflación'[IdPais];
  'País'[IdPais]
 )
)

Ventas USD CEAM =
'Inflación'[Ventas USD Panamá] +
'Inflación'[Ventas USD Costa Rica] +
'Inflación'[Ventas USD Honduras] +
'Inflación'[Ventas USD Nicaragua] +
'Inflación'[Ventas USD El Salvador] +
'Inflación'[Ventas USD Guatemala]

An the last one requested:

Inflación Resto =
CALCULATE(
 CALCULATE(
  SUM(
   'Inflación'[Inflacion]
  );
  LASTDATE(
   'Inflación'[Fecha]
  )
 );
 USERELATIONSHIP(
  'Inflación'[IdPais];
  'País'[IdPais]
 )
) * 100

About "The column IdZonaGestion of Zona-Gestión table, is not recognized by the measure.", what I mean is that when I start writting with the simple comma so the tables are displayed in the combo, the table "Zona-Gestión" doesn't even appear. What I did is write it without the autocomplete of PowerBI but the final formula marks an error in 'Zona-Gestión'[IdZonaGestion].

[Anonymous]

Maybe this pic helps to clarify what I need:

Thouse columns are all Measures. The last column "Inflación CEAM" is the sum of all previous Measures except "Inflación Resto". Now, what I would need is the principal Measure of "Inflación" that evaluates each row, and when the "DescZonaGestion" is CEAM-Roemmers or CEAM-Rowe the value shown must be the one of "Inflación CEAM", else, must be "Inflación Resto".

Hope this helps to understand my needs.

Thanks in advance.

[Vvelarde · Accepted solution]

@Anonymous

Hi,  The  'Zona-Gestión'[IdZonaGestion] needs a agreggation or in your case VALUES

IF(HASONEVALUE('Zona-Gestión'[IdZonaGestion]);
IF(VALUES( 'Zona-Gestión'[IdZonaGestion]) = "CEN-ROE" || VALUES ('Zona-Gestión'[IdZonaGestion]) = "CEN-ROW";
 'Inflación'[Inflación CEAM]);
 'Inflación'[Inflación Resto]
)

[Anonymous]

@Vvelarde, many thanks for your reply and solution! That was I have been looking for. These solved my problem.

Have a great day!