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"},0)))

 

Two columns

Employee ID

Employment Role (Full-Time; Part-Time, Casual)

 

There can be multiple records for each Employee ID; I want to see if any of those records are full time or part time. 

 

(as I continue to tear my hair out!)

 

Thanks All

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

8 Replies

  • 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"})
)
    • 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. 

      • d_gosbell's avatar
        d_gosbell
        Icon for Super User rankSuper User
        7 years ago
        Anonymous wrote:

        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. 

        But isn't that exactly what the code I provided is doing? As you can see below there is a 1 next to z12345681 and as you can see in the bottom table even though z12345678 has a 1 next to both the Full-Time and Part-Time rows the grand total at the bottom is still only 3 distinct employees.

         

         

        All I did was adjust the previous code I posted to match the column names in the sample data you provided:

         

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

        How are the results above different to what you require?