Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sumproduct formula in PowerBI

Hi All,    I have an excel formula I need to translate in DAX, anyone know how to do it?   =SUMPRODUCT(ISNUMBER(MATCH(EmployeeRange,A1,0))*ISNUMBER(MATCH(EmploymentRange,{"Full-Time","Part-Time"}...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    With this calculated column formula, here is the result i get

    Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[EmployeeID]=EARLIER(Data[EmployeeID])&&(Data[EmployeeRole]="Full-time"||Data[EmployeeRole]="Part-time")))>=1,1,BLANK())

d_gosbell's avatar
d_gosbell
Icon for Super User rankSuper User
7 years ago

I think you are probably after something like the following. It does a distinct count of EmployeeID where the current role is either Full-Time or Part-Time :

 

Full or Part Time Employees = CALCULATE(
    DISTINCTCOUNT(Table1[Employee ID]),
    FILTER( VALUES( Table1[Role] ), Table1[Role] in { "Full-Time","Part-Time"})
)