cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
svalen Member
Member

Context on Measure (with SUMMARIZE) - Same Table

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Context on Measure (with SUMMARIZE) - Same Table

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

svalen Member
Member

Re: Context on Measure (with SUMMARIZE) - Same Table

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