Table and Column variables won't work in DAX SUMX function

[alexanderholmes]

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

[srinivt · Accepted solution]

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

)