Dax Measure - Count How Many time Employee row has specific value

Question

I have a data set that looks like this;

EmpNumber| Symbole
122144| 9101
122144| 2101
122144| 9101
122144| 9101
144856| 2101
144856| 9101

I want to check for each Employee in my "PaymentsPrevMonthOnly" table (144856 & 122144) how many rows they have with the text value in Symbole column "9101" the means "Hourly Rate Component" symbole in my table. I have a part of formula, but it returns me an error.

Here is my DAX formula:

Hourly Rate Component Duplicate:=

var symbole= "9101"
var Emp = PaymentsPrevMonthOnly[EmpNumber]
return

CALCULATE(
    COUNTROWS(PaymentsPrevMonthOnly),
    PaymentsPrevMonthOnly[Symbole]= symbole && PaymentsPrevMonthOnly[EmpNumber]=Emp
)

Answer 1

Here is the simplest measure definition to count the number of rows where Symbole = "9101".

Hourly Rate Component Duplicate =
CALCULATE (
    COUNTROWS ( PaymentsPrevMonthOnly ),
    PaymentsPrevMonthOnly[Symbole] = "9101"
)

You don't need to specifically filter by Employee in the measure definition. When you add Employee in the visual, Power BI automatically calculate it for each Employee.

This line should be where you are getting the error:

var Emp = PaymentsPrevMonthOnly[EmpNumber]

This expression needs a "row context" for PaymentsPrevMonthOnly table in order to be a valid DAX expression which returns a scalar value. However, a measure does not have a row context, unless it has some iterator function (e.g. SUMX, FILTER).