Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 7
Anonymous
Not applicable

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
Not applicable

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?

Anonymous
Not applicable

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?

Anonymous
Not applicable

Thank you @tamerj1 ,

 

I will put something together and send it through. This might take a little while.

tamerj1
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors