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:
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)
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.