Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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),
...
)
Solved! Go to 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] )
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |