Sumproduct formula in PowerBI

[jest1234]

Hi All, 

I have an excel formula I need to translate in DAX, anyone know how to do it?

=SUMPRODUCT(--(ISNUMBER(MATCH(B28:B199999;C28:C49999;0))))

Two columns

april

may

There can be multiple records for each 

(as I continue to tear my hair out!)

Thanks All

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[jest1234]

Hi @Ashish_Mathur 

I'm sending exampele. I think this will do the trick 🙂

https://www.dropbox.com/s/3q085s2i5o7nogu/Data.xlsx?dl=0

tnx, for help

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[jest1234]

Thank you, that shuld be it.

Can you just tell me how did you do Data table.

[Ashish_Mathur]

Hi,

By appending Sheet1 and sheet2.  If my previous reply helped, please mark it as Answer.

[v-shex-msft]

HI @jest1234 ,

AFAIK, SUMPRODUCT function will multiplies ranges or arrays together and returns the sum of products. You can consider to use sumx function to get the result:

Formula =
SUMX ( ALLSELECTED ( Table ), [Column1] * [Column2] )

Regards,

Xiaoxin Sheng