Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Using variable name as column name in DAX

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

 

AccessControlTable 

MailIDEntityEntityValue
jan_doeUnitABC
joe_doeSubUnitDEF

 

ProjectDetails

ProjectCodeUnitSubUnit
PID100ABCDEF
PID200XYZABC

 

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

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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] )

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    3 years ago

    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] )