Context on Measure (with SUMMARIZE) - Same Table

[Anonymous]

Hi guys.

I am using a SUMMARIZE to do some calculations on Table1 with 2 levels of granularity:

   - ID1 for client (DimTable1)

   - ID2 for product (DimTable2)

Now I am doing a calculation to filter out clients with sales less than 200 (on all products);

RAPSI REAL = 
CALCULATE (
    SUM ( 'Table1'[RAPSI] );
    FILTER (
        SUMMARIZE (
            ALL (
                'Table1'[Client_ID];
                'Table1'[MonthYear]
            );
            'Table1'[ENTIDADE_ID];
            'Table1'[MonthYear];
            "MEASURESIVALUEPF"; SUM ( 'Table1'[SI VALUE PF] )
        );
        [MEASURESIVALUEPF] >= 200
    )
)

This works perfectly fine for me, however there is no product ID involved, so when I create a table visual with the product ID, it can't calculate.

I tried something like this but it doesn't work (added on bold):

RAPSI REAL = 
CALCULATE (
    SUM ( 'Table1'[RAPSI] );
    FILTER (
        SUMMARIZE (
            ALL (
                'Table1'[Client_ID];
                'Table1'[MonthYear]
            );
            'Table1'[ENTIDADE_ID];
            'Table1'[MonthYear];
            "MEASURESIVALUEPFPRODUCT"; SUM ( 'Table1'[SI VALUE PF] );
            "MEASURESIVALUEPFCLIENT"; CALCULATE(SUM('Table1'[SI VALUE PF]);ALL(DimTable[COD])));
        [MEASURESIVALUEPFCLIENT] >= 200

Any tips?

Thanks in advance.

[v-yuta-msft]

Hi svalen ,

'Table1' has relationship with 'DimTable', right? So which relationship have you built, one-to-many or many-to-one? In DAX, you should use one-side to filter many-side, inversely, there will be error.

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft, thanks for replying.

Yes, Table1 has relations with both Dim Tables, one to many. The code was a bit off, here it is corrected (what doesn't work is bold):

RAPSI REAL = 
CALCULATE (
    SUM ( 'Table1'[RAPSI] );
    FILTER (
        SUMMARIZE (
            ALL (
                'Table1'[Client_ID];
                'Table1'[MonthYear]
            );
            'Table1'[Client_ID];
            'Table1'[MonthYear];
            "MEASURESIVALUEPFPRODUCT"; SUM ( 'Table1'[SI VALUE PF] );
            "MEASURESIVALUEPFCLIENT"; CALCULATE(SUM('Table1'[SI VALUE PF]);ALL(DimTable1[Product_ID])));
        [MEASURESIVALUEPFCLIENT] >= 200