Forum Discussion
SUMPRODUCT with multiple filter in DAX
Hi Experts,
Please hava a look, Is it possible to convert the bellow formula into DAX,
=IFERROR(SUMPRODUCT(--(Table1[Date]=$B44),--(Table1[Col1]=$B$1),Table1[Col2],Table1[Col3])/SUMPRODUCT(--(Table1[Date]=$B44),--(Table1[Col1]=$B$1),Table1[Col3]),NA())
Regrads,
K.M.S.S
4 Replies
v-jiascu-msftMicrosoft Employee
Hi Anonymous,
Can you share a sample of your data? The pbix file would be great. We can't write a DAX formula without data.
Best Regards,
Dale
Hi Dale,
Thanks for your quick response.
Please find attached excel file:
https://drive.google.com/file/d/1CqjoexLq323bofzRyphDrPME8ux52vvX/view?usp=sharing
https://drive.google.com/file/d/1CqjoexLq323bofzRyphDrPME8ux52vvX/view?usp=sharing
I could not export the excel file data into PBI, as the file's data tab is interlinked with Table11 tab's data with Pivot table.
Please help me to create the complete tables in PBI with DAX.
Regards,
Salem
- v-jiascu-msft
Hi Anonymous,
Please check out the demo in the attachment.
1. It seems the formula isn't consistent with your data. So I can't build the DAX formula. Can you make it more clear, please?
2. We can import the excel data by the connector "Excel".
3. The table Data could be as follows. (I can't find "C11" and "C22" neither.)
Data = SUMMARIZE ( 'Table1', Table1[COL5], "sum of c11", SUM ( 'Table1'[COL11] ), "sum of c22", SUM ( Table1[COL12] ) )Best Regards,
Dale
- v-jiascu-msft
Hi Anonymous,
Did it help? Can you mark it as an answer?
Best Regards,
Dale