cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## 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

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.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors