Forum Discussion
How to return either a single text variable or multiple text variable with the IF function in DAX
Hi, Can anyone help me with this, I am trying to return either a single text variable or multiple text variable within an IF function in DAX. This would be based on a variable IsManager = 1 will return multiple values, if IsManager = 0 then return single text variable.
I am still learning so not sure if I am going about this the right way.
The following code gives an error: A table of multiple values was supplied where a single value was expected.
I have tried several combinations using brackets with no luck. I produced the following to simulate my code.
DEFINE
VAR MyDepartment = { "Department1" }
VAR DepartmentLvls = { "Department1", "Department2", "Department3" }
VAR IsManager = "1"
VAR GetDepartments = { IF ( IsManager = "1", DepartmentLvls, MyDepartment ) }
EVALUATE
GetDepartments
7 Replies
Hi Anonymous
Unfortunately, IF function deals only with scalar values and cannot accept tables. I hope one day DAX will have an IFTABLE function. If you can provide more context perhaps I can support you further.Thank you tamerj1 ,
That was what I was concerned with. My example is exactly that. I am using RLS and I need to determine if the UPN is a manager or not which is in a column against the user. If you are not a manager then the Department table is filtered through RLS with the users department. If you are a manager then the Department table is filtered through RLS with multiple departments which is a query on the department table. This is essentially what I am trying to achieve.
This is the the table query I am using in RLS.
VAR DepartmentLvls =
CALCULATETABLE (
DISTINCT ( VALUES ( DepartmentTable[Node] ) ),
CONTAINSSTRING ( DepartmentTable[Node], { DepartmentLvls } )
)Thank you kindly for your assistance.
Anonymous
can you provide a sample dummy data of the DepartmentTable?
