Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
7 years ago
Solved

Measure does not total and disappears values

Hi!   I have a very disgusting issue here:    First, the measure simply doesn't do the correct sum.    The strange thing is that measure already uses SUMX. I tried several things without ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi AlejandroPCar,

     

    If 73 is the right answer, please try to wrap the MIN part with "calculate". 

    Actividades Adultos Inmediato 2 =
VAR adult =
    SUMX (
        VALUES ( Adult_Quindio[id_persona] ),
        CALCULATE (
            SUM ( Jerarquia_Adult[cantidad] ),
            FILTER (
                Jerarquia_Adult,
                AND (
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) >= Jerarquia_Adult[edadinicial],
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) < Jerarquia_Adult[edadfinal]
                )
            )
        )
    )
RETURN
    adult

    Measure-does-not-total-and-disappears-values2

     

    BTW, maybe you can establish a *:* relationship to avoid a new table. But this is a suggestion based on the heavily truncated data.

     

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi AlejandroPCar,

 

This could be a context issue. I'm afraid you can't get your desired results. What would you like to get with them?

Let's have a look at the first issue.

Actividades Adultos Inmediato = 
    VAR adult = 
            SUMX(
                VALUES(Adult_Quindio[id_persona]),
                CALCULATE(
                    SUM(Jerarquia_Adult[cantidad]),
                    FILTER(
                        Jerarquia_Adult,
                        AND(
			// What do you think the MIN(Adult_Quindio[annodec]) should be?
			// It's two different values in the rows while it's a single value in the Total.
                            MIN(Adult_Quindio[annodec]) >= Jerarquia_Adult[edadinicial],
                            MIN(Adult_Quindio[annodec]) < Jerarquia_Adult[edadfinal]
                        )
                    )
                )
            )
RETURN
    adult

Measure-does-not-total-and-disappears-values

Actually, the result 72 is 36 * 2. Why? The context of MIN(Adult_Quindio[annodec]) is from VALUES(Adult_Quindio[id_persona]) that is a row context. MIN ignores the row context. So it's 58.25 in the Total and there are two pieces. 

You can adjust your formula according to the explanation above. Maybe you can post the idea that you are going to achieve.

 

BTW, please don't share any confidential data here.

 

 

Best Regards,

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
7 years ago

Hi! v-jiascu-msft

 

Wow, your explanation was incredibly useful. So... all the time the problem was the MIN... I am looking for a filter like "the value of the row 1 in the column A in the Table A is greater than the value of the row 1 in the column A in the Table B ". But, my question is now, to avoid MIN... How can I filter columns from two different tables in one FILTER? 

 

Something like...

 

CALCULATE(
	[Measure],
	FILTER(
		(Tables),
		TableA[Value] > TableB[Value]
		)
	)

 

 

I believed that MIN just give the value of that selected row. Well thats true when a single row is filtered, but... what a mistake. 

Thanks again...

 

And, I know it... all data I publish here is already heavy truncated.

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi AlejandroPCar,

     

    If 73 is the right answer, please try to wrap the MIN part with "calculate". 

    Actividades Adultos Inmediato 2 =
VAR adult =
    SUMX (
        VALUES ( Adult_Quindio[id_persona] ),
        CALCULATE (
            SUM ( Jerarquia_Adult[cantidad] ),
            FILTER (
                Jerarquia_Adult,
                AND (
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) >= Jerarquia_Adult[edadinicial],
                    CALCULATE ( MIN ( Adult_Quindio[annodec] ) ) < Jerarquia_Adult[edadfinal]
                )
            )
        )
    )
RETURN
    adult

    Measure-does-not-total-and-disappears-values2

     

    BTW, maybe you can establish a *:* relationship to avoid a new table. But this is a suggestion based on the heavily truncated data.

     

     

    Best Regards,

    • AlejandroPCar's avatar
      AlejandroPCar
      Icon for Helper IV rankHelper IV
      7 years ago

      Amazing. Can you explain the reasson of put MIN in a CALCULATE?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi AlejandroPCar,

         

        Usually, calculate-function-dax is used to alter the current context. It also can convert a Row context to the Filter context in which the MIN can evaluate row by row.

         

         

        Best Regards,