Forum Discussion
Table and Column variables won't work in DAX SUMX function
Hi All!
(apologies, had to post again for some reason!)
Very new to Power BI, I've made some progress on the learning front, but seems like I've hit a snag!
I have created a query which takes all of the column headers from a data table (tblRawData) and populates them into tblheaders in the Name Column (tblHeaders[Name]) and this is used in a slicer.
Users can then use this slicer to select one of the header names from the list to change the data that's displayed in a column chart.
I have created a measure which works when I have hard-coded table and column names, but when I try and use variables in place of these it throws the following error.
I've tried many different combinations, but can't seem to get it to work and after a fair bit of searching to no avail, I thought I'd post on here. Any help would be greatly appreciated.
Below is my code (I've changed the name of bits for clarity):
Measure =
VAR SelectedSlicerValue = SELECTEDVALUE(tblHeaders[Name])
VAR ColumnReference = "[" & SelectedSlicerValue & "]"
Return
SUMX(tblRawData,ColumnReference))
DAX SUMX function can only aggregate numeric values and doesn't support string columns, which is what the error is telling you.
The problem here is that you are trying to a use VARIABLE to dynamically pick a column reference from the model, which currently is not a capability exposed by DAX. Having said that, you could potentially, do something like this instead (though it is not fully dynamic):
SWITCH(
SELECTEDVALUE(tblHeaders[Name]),
"Header1",
SUM(tblRawData[Header1]),
"Header2",
SUM(tblRawData[Header2]),
BLANK()
)
1 Reply
DAX SUMX function can only aggregate numeric values and doesn't support string columns, which is what the error is telling you.
The problem here is that you are trying to a use VARIABLE to dynamically pick a column reference from the model, which currently is not a capability exposed by DAX. Having said that, you could potentially, do something like this instead (though it is not fully dynamic):
SWITCH(
SELECTEDVALUE(tblHeaders[Name]),
"Header1",
SUM(tblRawData[Header1]),
"Header2",
SUM(tblRawData[Header2]),
BLANK()
)