DAX Function to Substitute Excel SumProduct involving multiple conditions

I have a fairly large table in Excel which is linked to my Power Pivot as data source.

I have the following function in the Excel Table:

=SUMPRODUCT(--(fdataTable1[Passenger phone number]=[@[Passenger phone number]]),--(fdataTable1[Completed Time]<[@[Completed Time]]))

This function is making my Excel sheet run slow, therefore I was looking for a way to get rid of this column in the source excel file and instead create the same in Power Pivot as a Calculated Column.

Some help would be appreciated.

Hi,

You can use something like the attached.

I have had to paste the formula into a text file in an effort to bypass the forum's ridiculous firewall.

@xlnitwit - If you place a space behind each "<", you can post it:

Please Login or Register  to view this content.

Otherwise, Securi thinks it's HTML and rejects it.

Thanks- I thought I had tried that, but perhaps not. I seemed to be blocked from the site for about 10 minutes after a few attempts so maybe that was the problem.

&& is the operator equivalent of the AND function.
You have to use < because you want to count items with an earlier completed time. The EARLIER function allows you to use the earlier row context (the current row in the table) when iterating through each row in the table using FILTER.

The table order is irrelevant. The EARLIER function does not relate to row order but to the row context for evaluation.

For a calculated column, the formula is evaluated for each row in the table. That is the primary row context.

In this formula, we filter the table evaluating each row against some criteria- that creates a second row context. In order to compare each row in this second context against the value from the first context, we use the EARLIER function to return the value from the first context. It doesn't mean that only rows that come before the current row in the table are considered.

It's a little bit like using the @ symbol in a structured table reference to mean 'this row' rather than the entire column.

Originally Posted by ibuhary

there is soooooooooooo much to learn that we dont know.

Too true- especially as the BI features in Excel are a moving target! Case in point, if you have Excel 2016 you could use VAR instead of EARLIER.