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
AlejandroPCar
Helper IV
Helper IV

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. 

sumamala.png

 

The strange thing is that measure already uses SUMX. I tried several things without success. Then, the problem got worse. The highlighted measure when is under a low-level filter shows the data. But when the measure is without any filters the data simply disappear.  

errordeagregacion.png

The goal of that measure is to show the number of filtered movements. The first measure is the total of movements. The wrong one is the filtered movements. 

 

Well, that's all. I know the importance of the sample of data so my little file can be found here: 
https://1drv.ms/u/s!AtTnrgPUQzQCgrN2hHAzvVKnJqBt9Q

Hope you guys can help me. 

 

Thanks.

 

 

 

1 ACCEPTED SOLUTION

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

 

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.