Hi, I am trying to use column name as a variable in a dax expression to create a table. The table returns no row and no error, but the query without variable return a row.
The query below returns a row
TestTable =
var b =
CALCULATETABLE(
SELECTCOLUMNS(‘Hours Table’,
“test”,‘Hours Table’[Hours]),
FILTER(‘Hours Table’,‘Hours Table’[Team] = “a”)
)
return
b
This query returns no row
TestTable2 =
var t = “‘Hours Table’[Team]”
var b =
CALCULATETABLE(
SELECTCOLUMNS(‘Hours Table’,
“test”,‘Hours Table’[Hours]),
FILTER(‘Hours Table’,t = “b”)
)
return
b
The reason why the first query is returning the row is because an expression has been used inside the variable.
Most importantly, one cannot reference the naked column from any table inside the variable since that column name itself will be greyed out and still if you go ahead, it will give the error (Even you would have faced the same issue, wouldn’t you?). The reason why it get’s greyed out is because inside the variable either you can use the expression i.e., the first query which you’ve created or write a sting of text or numbers inside it.
And based on the above para, you’ve done the exact same thing. That is, putting the column name from a table inside the quotation marks (" ") and therefore it’s no longer a column from a table, it’s just a mere text string. And therefore, it’s not satisfying the condition inside the “FILTER()” function and thus returns the result as a BLANK.
I’m providing a link below of the documentation from the Microsoft pertaining to the Variables (Check the “Remarks” section “Point No. 4”). And also a link of a blog from Enterprise DNA based on this very topic.
Hoping this helps you in your understanding about the Variables.
Hi @akeko,
In the second measure:
TestTable2 =
var t = “‘Hours Table’[Team]”
var b =
CALCULATETABLE(
SELECTCOLUMNS(‘Hours Table’,
“test”,‘Hours Table’[Hours]),
FILTER(‘Hours Table’,t = “b”)
)
return
b
It is not possible to define a variable that references a column name as a character string. The filter condition t = “b” always evaluates to false since the string “‘Hours Table’[Team]” <> “b”.
To refer to a table column in a variable, we have to define the variable inside the FILTER function.
TestTable3 =
VAR _filter =
FILTER(
‘Hours Table’,
VAR t = ‘Hours Table’[Team]
RETURN
t = “b”
)
VAR b = CALCULATETABLE(
SELECTCOLUMNS(
‘Hours Table’,
“Test”,‘Hours Table’[Hours]
),
_filter
) return b
You could assign the column name with a SWITCH if you need this assignment to be dynamic based on a slicer.
Greetings,
Hi @jafernandezpuga Thanks for the response , the suggestion works, but when I try to pass it into a switch parameter I have this error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Hi @akeko,
The value returned by the SWITCH function is a scalar, so it does not allow us to return the table returned by the FILTER function.
You can create a single variable for the filter and use the SWITCH to assign the corresponding value based on the selected value:
VAR _SelectedValue =
“A”
VAR _filter = FILTER(
‘Hours Table’,
VAR t = ‘Hours Table’[Team]
RETURN
SWITCH(
TRUE(),
_SelectedValue = “A”, t = “a”, _SelectedValue = “B”, t = “b”, t = “a”
)
)
VAR b = CALCULATETABLE(
SELECTCOLUMNS(
‘Hours Table’,
“Test”,‘Hours Table’[Hours]
),
_filter
) return b
Thanks for the time @jafernandezpuga the switch seems to default to the last value of nothing been selected, although I am sure that the switch function returns the expected value on its own
Hi @akeko,
Since this is a dynamic calculation that depends on the value that we select in the report, we have to create the table in a measure and return the sum of the column, instead of creating the table in the model.
The measure is called Total Test.
Greetings,
