Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
Oops.. that code should be
CALCULATETABLE (
DISTINCT ( VALUES ( DepartmentTable[Node] ) ),
CONTAINSSTRING ( DepartmentTable[Node], { MyDepartment} )
)
@Anonymous
can you provide a sample dummy data of the DepartmentTable?
Sure. Basically, If the user has lets say the node .2521.3456.1885. as his department then he should only see that department in the reports. If the user is also a Manager then they should see all the departments starting from that node. Hence example below. The Nodes are text. If you are interested I could put up some cleansed cut back version in pbix.
.2521.3456.1855. |
.2521.3456.1855.1643. |
.2521.3456.1855.1643.1976. |
.2521.3456.1855.1643.2081. |
.2521.3456.1855.1643.2226. |
etc...
@Anonymous
Yes please do.
How is a manager defined in your data?
Thank you @tamerj1 ,
I will put something together and send it through. This might take a little while.
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |