Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.

Show and Tell
Tips and Tricks

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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