If/Then logic to create a table in DAX

[jflipse]

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

[v-kkf-msft]

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

[Whitewater100]

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

[daXtreme]

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 

[jflipse]

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.

[daXtreme]

NO. That's not possible.

[HotChilli]

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