Forum Discussion
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"}...
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())
Ashish_Mathur
Super User
Super UserHi,
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_gosbellSuper User
Super UserOr if the output Ashish provided is what you want you could do the same in a measure by extending the previous approach as follows:
Full or Part Time Employees = CALCULATE(
DISTINCTCOUNT(Table1[EmployeeID]),
FILTER( ALL( Table1[EmployeeRole] ), Table1[EmployeeRole] in { "Full-Time","Part-Time"}),
ALL(Table1),
VALUES(Table1[EmployeeID])
)