Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
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
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
User | Count |
---|---|
141 | |
70 | |
70 | |
53 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |