Forum Discussion
Sumproduct formula in PowerBI
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())
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.
Super User
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?