Anybody, Who knows countif from another table?

[saranp780]

I am a new user from Power BI. I tried to use count DAX like COUNTIF in Excel but now, I can't do it. For example, if I have 2 tables of data

Table 1 (sheet1)

(A)                                 (B)

username                     Name of staff

Account01                        Narinthip

Account02                        Edward

Account03                        John

Engineer01                       Sarin

Engineer02                       Tom cruise

Engineer01                       Cavalry

Account02                        Sarin

Account03                        Cavalry

Table 2 (sheet2)

 (A)                                (B)

User                             Owner

Account01                  Account Dep.

Account02                  Account Dep.

Account03                  Account Dep.

Engineer01                 Engineer Dep.

Engineer02                 Engineer Dep.  

So I want to count user from table 2 that how many people use per user? Such as Account02 use two people.

If in Excel I can use Countif (formula) =COUNTIF(SHEEET1!$A:$A,Sheet2!$A:$A)

I want to do this also in Power BI.

Best regard.

[Datatouille · Accepted solution]

Hi,

First, create a relationship between Table2[User] and Table1[UserName] fields.

Second, create the following measure: Cnt = Countrows(Table1)

Third, create a matrix visual, use  Table1[UserName] field as X-Axis and the measure "Cnt" in the values field of this matrix.