Hey guys,
I’m currently working with two tables. In my EMPLOYMENTS table, the Member_C column contains the User IDs of people who have/had a job. The User ID appears once for each job a person have/had. In my INDIVIDUAL table, the ID column lists all User IDs in the system. I want to set up a new column in the INDIVIDUAL table that counts the number of times a User ID appears in the EMPLOYMENTS table if it matches the User ID of each row in the INDIVIDUAL table.
I know how to do this in Excel, but I’m very new in using DAX functions. I need to get the DAX equivalent for this formula:
=COUNTIF('EMPLOYMENTS'!B$1:B$10,D9)
Looking forward to your suggestion/s!
A demo example model is always helpful to get a quicker and better answer…but I’ll try anyway to give you something to work with.
Firstly you don’t need to use COUNTIF like logic in Power BI. The data model does all the hard work for you.
You could potentially just use something like this below
CountUser =
CALCULATE(COUNTA(EMPLOYMENTS[Member_C]),
FILTER( ALL(EMPLOYMENTS), EMPLOYMENTS[Member_C] = INDIVIDUAL[User_ID] ) )
This is guess though as don’t know the total setup.
The key here is to understand how the natural context is working in behind the scenes with your model. This does most of the filtering for you.
Then if you need to apply any further filters you can do that using a combination of CALCULATE and FILTER somehow.
Here’s some videos that I think will help you here.
http://portal.enterprisedna.co/courses/103686/lectures/1772351
http://portal.enterprisedna.co/courses/103686/lectures/1772358
http://portal.enterprisedna.co/courses/103686/lectures/1772402
See how you go with these.
Thanks
Sam
