Search code examples
powerbidaxssasssas-tabular

DAX. Conditional Measure

I'm trying to create a measure that returns a Sum or an Average, depeding on the value of a column. I understand that you need to give a row context to the expression, but I don't like the idea of doing and averege on an id column This is my code:

ValorHandle = IF (EVENTOS_DELTA_HD[idGlo] = 11,SUMX(EVENTOS_DELTA_HD,EVENTOS_DELTA_HD[Valor]),FORMAT(AVERAGEX (EVENTOS_DELTA_HD,EVENTOS_DELTA_HD[Valor]),"Percent"))

This throws this error:

A single value for column ‘idGlo’ in table ‘EVENTOS_DELTA_HD’ 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.

If I do something like

ValorHandle = IF (AVERAGE(EVENTOS_DELTA_HD[idGlo]) = 11,SUM(EVENTOS_DELTA_HD[Valor]),FORMAT(AVERAGE (EVENTOS_DELTA_HD[Valor]),"Percent"))

the error goes away, but is just makes no sense doing the average of an id field. This would just break if the ID was not numerical.

Any idea on how to fix this? idGlo, just tells me the type of calculation I need to apply to my "Valor" column (SUM or AVERAGE). Also even when using average(idGlo), measure is not correct, when table relationships are involved

Let me add that I cannot do 2 different measures, because I need to show averages and sums in the same report elements

Mixing different calculations

In this example, if the "Nombre" column come from a related table, the report just breaks, as it does not respect the relation between "HANDLE" and "Nombre" when adding the measure

Solution

  • Change AVERAGE(EVENTOS_DELTA_HD[idGlo]) = 11 with SELECTEDVALUE(EVENTOS_DELTA_HD[idGlo])=11