Using variable name as column name in DAX

[Anonymous]

I'm trying to build dynamic RCL in PowerBI. Below is my table structures.

AccessControlTable 

MailID	Entity	EntityValue
jan_doe	Unit	ABC
joe_doe	SubUnit	DEF

ProjectDetails

ProjectCode	Unit	SubUnit
PID100	ABC	DEF
PID200	XYZ	ABC

RLS DAX Query:

=
VAR NEWTABLE = CALCULATETABLE (AccessControlTable,PATHCONTAINS(LOWER(AccessControlTable[MailID]),
SUBSTITUTE(LOWER(USERNAME()),"itlinfosys\","")))

VAR ENTITY = SELECTCOLUMNS(NEWTABLE,"Entity", [Entity])
VAR ENTITYVALUE = SELECTCOLUMNS(NEWTABLE,"EntityValue", [EntityValue])

RETURN CONTAINS(ProjectDetails,ProjectDetails[ENTITY],ENTITYVALUE) // ENTITY is the variable name

Above code doesn't work, throwing error as below.

I tried using `SWITCH` and it works, but since I have 100s of ENTITY types it's not efficient method.

RETURN SWITCH(
    TRUE(),
    ENTITY = "Unit", CONTAINS(ProjectDetails,ProjectDetails[Unit],ENTITYVALUE),
    ENTITY = "SubUnit", CONTAINS(ProjectDetails,ProjectDetails[SubUnit],ENTITYVALUE),
    ENTITY = "Category1", CONTAINS(ProjectDetails,ProjectDetails[Category1],ENTITYVALUE),
    ENTITY = "Category2", CONTAINS(ProjectDetails,ProjectDetails[Category2],ENTITYVALUE),
    ...
)

[AlexisOlson · Accepted solution]

I don't think it's possible to dynamically reference a column using a string variable in DAX, unfortunately. (Anyone please correct me if this is not true.)

I'd recommend unpivoting your ProjectDetails table to match the shape of the AccessControlTable like this:

Then you should be able to write something like

CONTAINSROW ( { ENTITY, ENTITYVALUE }, ProjectDetails[Entity], ProjectDetails[EntityValue] )