Measure Context

[Anonymous]

Hi guys.

I am creating a table I want to use on a visual based on this (nevermind the SUMMARIZECOLUMNS part, the result is the same, wrong):

TEMP CLUSTER SI2 = 
SUMMARIZE (
    ALL ( 'FACT'[ENTIDADE_ID] );
    'FACT'[ENTIDADE_ID];
    "SI UNITS"; [SI UNITS PF];
    "SI VALUE"; [SI VALUE PF]
)

/*
SUMMARIZECOLUMNS (
    'FACT'[ENTIDADE_ID];
    "SI UNITS"; [SI UNITS PF];
    "SI VALUE"; [SI VALUE PF]
)
*/

The measures used inside are like this:

SI UNITS PF =
CALCULATE (
    SUM ( 'FACT'[QTD_FATURADA] );
    OR (
        'FACT'[Sentido] = "SAIDA";
        'FACT'[Sentido] = "FATURA"
    );
    'FACT'[ENTIDADE_ID] <> 1
)

SI VALUE PF =
CALCULATE (
    [COMPRA_L];
    OR (
        'FACT'[Sentido] = "SAIDA";
        'FACT'[Sentido] = "FATURA"
    );
    'FACT'[ENTIDADE_ID] <> 1
)

COMPRA_L = SUMX('FACT';[QTD_FATURADA]*[PGRUPO])

This is the result. The measures loose all the context and return the total value.

What's going on here? I created this table before with SUMS (when I didn't have the measures created) and it didn't lose context back then.

Thanks in advance.

Cheers!

[v-piga-msft]

Hi @Anonymous,

From your dax formulas, the syntax is right.

I also made a test with the formula similar with yours, here is my output.

If it is convenient, could you share some data sample and your expected output, so that we can help further investigate on it? 

Best Regards,

Cherry

[Anonymous]

@v-piga-msft

If you still have the sample file, could you try to change the conditions to another table please? Inside the FILTER part on CALCULATE, reference a Table2 (which has good relations). Like:

Measure =
CALCULATE (
    SUM ( 'Table1'[Sales] ),
    OR ( 'Table1'[Type] = "C", 'Table1'[Type] = "D" ),
    'Table2'[ID2] <> 3
)

Because I think the problem is when you reference external tables in the SUMMARIZE.

Cheers!

[v-piga-msft · Accepted solution]

Hi @Anonymous,

My sample data may could not reproduce your secnario. To get the solution, couuld you share your date sample and your expected ouput?

Best  Regards,
Cherry

[Anonymous]

Hi @v-piga-msft

I have been researching a lot and on another post, @anandav suggested this:

https://www.sqlbi.com/articles/variables-in-dax/

Which cleared some things on my messy head.

I was misusing variables. sorry.

Thanks a lot for your help.

Cheers!