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())

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks so much, 

 

Sort of what I am looking for, however. As per the table below z12345678 has multiple roles across multiple departments. I want to check where there are multiple records for a particular Employee, whether one of them is full time or part time. 

EmployeeID	EmployeeRole	Dept.
z12345678	Casual	1
z12345678	Full-Time	2
z12345678	Part-Time	3
z12345678	Casual	3
z12345679	Casual	2
z12345680	Full-Time	3
z12345681	Casual	1
z12345681	Full-Time	2

I need to check where there are multiple records that one of them is Full-time. For z12345681 I know one of the records is Full-Time so I would get a 1 in excel but cant figure out how to do it in Power BI. 

 

Counting the records won't give me a one step solution, but......... using the formula you provided I could create another column with the total count and then subtract from that, I would just get some minus one figures when I just have one record for a casual staff member which means I would need another step to clean that up. 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
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

    Or 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])
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Ashish_Mathur  This works like an absolute charm. Thanks so very much for your time on this, its greatly appreciated. Its exactly what I need, a 1 or a 0 aganst each employee ID. 

     

    Really genius! 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      You are welcome.  Thank you for your kind words.