Looking for formula SUMPRODUCT(1/COUNTIF(data,data)) in power BI

[Anonymous]

Hi everyone,

is there anyone know what the calculation is in power bi for this calculation in Excel?

SUMPRODUCT(1/COUNTIF(data,data))

Thanks in advance

[Anonymous]

I did a calculation in Excel by using the formula SUMPRODUCT(1/COUNTIF(data,data)) to calcualte the Column D. Could you please help me to do the same calculation in Power BI?

@edhans 

Or, I could also use key to calculate it. I think it will easier to do it in power BI.

[edhans · Accepted solution]

The SUMPRODUCT isn't doing anything in your formula. Remove it in Excel and you'll get the same.

As for doing this in DAX, pseudocode would be something along the lines of this for a calculated column. 

IsUnique = 
VAR ThisDate = [Date]
VAR ThisName = [Name]
VAR RecordCount =
    COUNTROWS(
        FILTER(
            'Table',
            [Date] = ThisDate && [Name] = ThisName
        )
    )
RETURN
DIVIDE(1, RecordCount)

[Anonymous]

Thanks 

it is very useful

[amitchandak]

You can have like

measure = sumx(Table,1/(if(not(isblank(table[column])),table[column],blank())))

Or
measure = sumx(Table,divide(1,table[column]))

[edhans]

What does that do in Excel? If CountIF() returns 3, then it is sumproduct(1/3) which is sumproduct(.33333) which is just .33333. Sumproduct isn't doing anything.

Can you provide us some sample data and expected output? Power BI doesn't work like Excel. Tables in Power BI are not the same thing as spreadsheets so sometimes using Excel equivalent formulas is the wrong approach.