cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## If/Then logic to create a table in DAX

Hello,

I am trying to create a table dynamically based on the value of a measure, in this case boolean measure IsCSS.  Here is the DAX I used to test out the concent.  I am getting the "...expression refers to multiple columns.  Multiple columns cannot be converted to a scalar value." error.  I am choosing to create this as a table under Modeling >> New Table, so I am not sure why it thinks I am returning a single value measure when I am trying to return a table.  Any thoughts would be greatly appreciated.  Thanks!

TestTable =
//test if an IF statement can coexist with a CALCULATETABLE statement
VAR Tbl1 =
IF([IsCSS] = TRUE(),
CALCULATETABLE(
'Home Care',
'Home Care'[PHY_NPI]
),
CALCULATETABLE(
'Home Care',
'Home Care'[Physician Name]
))

RETURN
Tbl1

6 REPLIES 6
Community Support

Hi @jflipse ,

You cannot implement RLS using a calculated table. RLS is a restriction on the rows in the table, so could you please share your example data and expected output?

Best Regards,
Winniz

Solution Sage

Hi:

I beleive the Boolean filters used in Calculate and CALCULATABLE are more of a column variety and not measues.

E,G. Stock{Item Price] > 10. Both these functions turn the Boolean results into tables.

With the Stock Item Example

High Priced Items = CALCULATETABLE(
Stock,
Stock[Unit Price] > 500
)

To write your formula(measure) this may work:(would need to see the data)

FILTER(
SUMMARIZE(‘Home Care’,
'Home Care'[PHY_NPI],

“IS CSS”, [IsCSS]),
[Is CSS] = “TRUE”)

Solution Sage

Adding to what's already been said by @HotChilli...

You cannot return a table from IF. Only scalars. Secondly, CALCULATETABLE takes types of arguments that are filters or boolean expressions. Your code does not adhere to the syntax. Last thing... model tables cannot be dynamic. They're always static, so no matter what you do, you cannot change such a table depending on any measure. Such tables are recalculated only once: when you refresh your data. Then they're set in stone till the next refresh.

For syntax please consult https://dax.guide/calculatetable

Thanks.  What I am trying to do is identify the type of user logged in when using RLS and then have the table populated for one type of user (non-CSS) and empty for the other (CSS).  The booloean is used only to identify which it is when the user is logged in, and then would create the table with data or empty.

Solution Sage

NO. That's not possible.

Super User

It's not good practice to use a measure in a calculated column or calculated table.

I don't know if this is just an exercise but maybe there's another solution to the issue you're trying to solve

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors