Apperantly identical queries, different results

[daniq]

I have these two measures:

$ Costo No Filter = CALCULATE(SUM(EjecPtal[Valor COP])*-1,USERELATIONSHIP(Dates[Date], EjecPtal[Date]))

$ Costo Comercial = CALCULATE(SUM(EjecPtal[Valor COP])*-1, FILTER(EjecPtal, EjecPtal[CLIENTE] = "COMERCIAL"), USERELATIONSHIP(Dates[Date], EjecPtal[Date]))

I can't understand why the second one would only return a total correctly, but it won't work at row level:

For the record, I could also filter by other column than client, but I it still won't work as I expect. For example:

This works as expected:

$ Costo Externo = CALCULATE(SUM(EjecPtal[Valor COP])*-1, USERELATIONSHIP(Dates[Date], EjecPtal[Date]), FILTER(EjecPtal, EjecPtal[SUBORIGEN] = "COSTO EXTERNO"))

This still lacks row level detail, only works as a total:

$ Costo Comercial 2 = CALCULATE(SUM(EjecPtal[Valor COP])*-1, USERELATIONSHIP(Dates[Date], EjecPtal[Date]), FILTER(EjecPtal, EjecPtal[SUBORIGEN] = "Costo nómina comercial")) 

 

[FlipFlop1]

Does this will show at row level, but not at total?
$ Costo Comercial = 
VAR one = CALCULATE(SUM(EjecPtal[Valor COP])*-1, USERELATIONSHIP(Dates[Date], EjecPtal[Date])
RETURN
IF(EjecPtal[CLIENTE] = "COMERCIAL",  one, BLANK())

[daniq · Accepted solution]

This won't work, buuuut it leaded me to play with chatgpt and find out the solution!

Basically CALCULATE can take a filter as a parameter:

$ Costo Comercial = CALCULATE(
    SUM(EjecPtal[Valor COP]) * -1, 
    USERELATIONSHIP(Dates[Date], EjecPtal[Date]), 
    EjecPtal[CLIENTE] = "COMERCIAL"
)

A final note would be that DAX Query View will write EXACTLY the same for both:

 $ Costo Comercial = CALCULATE( SUM(EjecPtal[Valor COP]) * -1,  USERELATIONSHIP(Dates[Date], EjecPtal[Date]),  EjecPtal[CLIENTE] = "COMERCIAL")

$ Costo Comercial 2 = CALCULATE(SUM(EjecPtal[Valor COP])*-1, FILTER(EjecPtal, EjecPtal[CLIENTE] = "COMERCIAL"), USERELATIONSHIP(Dates[Date], EjecPtal[Date]))

DEFINE
    VAR __DS0FilterTable = 
        TREATAS({"COMERCIAL"}, 'EjecPtal'[CLIENTE])
    VAR __DS0FilterTable2 = 
        TREATAS(
            {"Ene. 2024",
                "Feb. 2024",
                "Mar. 2024",
                "Abr. 2024",
                "May. 2024",
                "Jun. 2024",
                "Jul. 2024"},
            'Dates'[Month & Year]
        )
    VAR __DS0FilterTable3 = 
        FILTER(
            KEEPFILTERS(VALUES('Project'[Name])),
            NOT('Project'[Name] IN {"Base Project Template",
                "Project 1"})
        )
EVALUATE
    SUMMARIZECOLUMNS(
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        "v__Costo_Comercial", IGNORE('EjecPtal'[$ Costo Comercial]) // here it would change the names to Costo Comercial 2
    )

And the table will call both with the same context:

DEFINE
    VAR __DS0FilterTable = 
        TREATAS({"COMERCIAL"}, 'EjecPtal'[CLIENTE])
    VAR __DS0FilterTable2 = 
        TREATAS(
            {"Ene. 2024",
                "Feb. 2024",
                "Mar. 2024",
                "Abr. 2024",
                "May. 2024",
                "Jun. 2024",
                "Jul. 2024"},
            'Dates'[Month & Year]
        )
    VAR __DS0FilterTable3 = 
        FILTER(
            KEEPFILTERS(VALUES('Project'[Name])),
            NOT('Project'[Name] IN {"Base Project Template",
                "Project 1"})
        )
    VAR __DS0Core = 
        SUMMARIZECOLUMNS(
            ROLLUPADDISSUBTOTAL(
                ROLLUPGROUP('Dates'[Month & Year], 'Dates'[Month], 'EjecPtal'[CLIENTE], 'Dates'[Date]), "IsGrandTotalRowTotal"
            ),
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable3,
            "v__Costo_Comercial_2", 'EjecPtal'[$ Costo Comercial 2], 
            "v__Costo_Comercial", 'EjecPtal'[$ Costo Comercial]
        )
    VAR __DS0PrimaryWindowed = 
        TOPN(
            502,
            __DS0Core,
            [IsGrandTotalRowTotal],
            0,
            'Dates'[Month],
            1,
            'Dates'[Month & Year],
            1,
            'EjecPtal'[CLIENTE],
            1,
            'Dates'[Date],
            1
        )
EVALUATE
    __DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Dates'[Month],
    'Dates'[Month & Year],
    'EjecPtal'[CLIENTE],
    'Dates'[Date]

Really ood behaviour, I obviously must be unseeing something. Even GPT agrees that using FILTER would work better at row level rather that filtering as a parameter to CALCULATE.

[AnkitKukreja]

Hi! @daniq 

This could be due to some gap in data model relationship or some filters flowing through, if you can share the sample data with desired solution then I can have a look at it. Also, make sure you are not sharing in sensitive data.

[daniq]

HI, I've just found out the solution and posted it to FLiflop1 answer. It was indeed a filtering issue, just can't understand why exactly.

Thanks!