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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 60 members 1,246 guests
Please welcome our newest community members: