topic Re: If/Then logic to create a table in DAX in DAX Commands and Tips

If/Then logic to create a table in DAX

jflipse — Fri, 20 May 2022 16:20:46 GMT

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

Re: If/Then logic to create a table in DAX

HotChilli — Fri, 20 May 2022 16:45:01 GMT

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

Re: If/Then logic to create a table in DAX

daXtreme — Fri, 20 May 2022 17:31:32 GMT

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

Re: If/Then logic to create a table in DAX

jflipse — Fri, 20 May 2022 17:45:30 GMT

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.

Re: If/Then logic to create a table in DAX

Whitewater100 — Fri, 20 May 2022 17:46:18 GMT

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”)

Re: If/Then logic to create a table in DAX

daXtreme — Fri, 20 May 2022 17:46:49 GMT

NO. That's not possible.

Re: If/Then logic to create a table in DAX

v-kkf-msft — Wed, 25 May 2022 05:41:01 GMT

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