Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |