Assign category code based on membership

DAX DAX Calculations
1

Hello, I am trying to get PowerBI to automatically assign a category code of “staff” or “instructor” to a list of names in table CFTPO based on whether that name is part of a separate list of names in table DMTC position roster. If the CFTPO name appears in the DMTC position roster it gets assigned staff, otherwise instructor. Ultimately I want to be able to filter visuals based on a slicer with the staff and instructor options. I created this measure:

Role2 =
VAR DMTC = VALUES(‘DMTC position roster’[Staff Last Name])
VAR CFTPOname = VALUES(CFTPO[NOM_EMP_SURNAME])
VAR rolecode = IF(COUNTROWS(INTERSECT(DMTC,CFTPOname)),1,2)
RETURN
SWITCH(rolecode,1,“Staff”,2,“Instructor”)

As a measure it works fine. I throw it into a table visual and it assigns staff or instructor correctly to each name. However it won’t allow me to user a slicer since I can’t establish a relationship between a measure and a table. So I created a calculated column in the CFTPO table and pasted the DAX formula above into it. However, it returned a column in which every name was assigned staff.

Any help to either get this formula to work or a different solution will be appreciated.

Thanks

Mark

2

= if(CALCULATE( COUNTROWS(CFTPOname), FILTER( CFTPOname, (‘DMTC position roster’[Staff Last Name] = EARLIER(‘DMTC position roster’[Staff Last Name] ) ) > 0, "Staff", "Instructor")

a pbix file will help but check the above dax to see if you can created a calculated column on DMTC Position Roster Table

3

@Mombo,

Welcome to the forum – great to have you here!

@C9411010 buzzed in first, but I thought it would be helpful to post my solution anyway since we took different approaches. I constructed the following example from some existing data on a previous post. The first variable is just to construct a sample DTMC table against which the full roster will be checked. The useful part of this for you I think will be the use of the IN construct:

Assign Role = 

VAR DTMTC =
CALCULATETABLE(
    VALUES( Employees[Name] ),
    Employees[Employer] = "Self Employed"
)

VAR Role = 
IF( 'CFPTO'[Name] IN DTMTC,
    "Staff",
    "Instructor"
)

RETURN
Role

image

Here’s a great article on the uses of the IN operator:

I hope this is helpful. Full solution file posted below.

4

C9411010, thanks for the solution. Test intersection.pbix (43.2 KB) I couldn’t get this formula to work as I wasn’t allowed to enter column names in certain areas. It would only accept a table name. I will upload the .pbix file. Although Brian J’s solution worked for me, I would still like to learn this approach to broaden my understanding. If you get it to work, could you please explain why it works.

Brian, thanks for the simple solution. Just had to modify the VAR DTMTC formula and it worked.

5

StaffType

if(CALCULATE( COUNTROWS(‘DMTC position roster’), FILTER( ‘CFTPO Role’, (CFTPO[NOM_EMP_SURNAME] = EARLIER(‘CFTPO’[NOM_EMP_SURNAME] ) ))) > 0, “Staff”, “Instructor”)

Here you go , file attachedTest intersection.pbix (37.4 KB)

The earlier() FUNCTION is the key. It is a fairly advanced function to understand but sam does a wonderful job of explaining

6

Thanks, it works now. I watched the video, but I am going to have to study the solution for awhile to understand how it works.

7

Use the Variable as suggested by Sam. It is a little bit easier to implement using variable. However understanding the Earlier() function is critical to understand evaluation context in DAX. All the best.