Use variable as column name

[nolos]

I'm completely lost here. I have this table called "Hours"

and I am trying to create a measure in my visual that shows the hours depending on the location. Like this.

So, whatever location is showing the the "location" row in the visual, I want to sum that column in the Hours table. Something like this

var loc = Locations[Location]

return

sum of hours = Sum(Hours[loc])

Is there anyway to use a variable as the culumn name so that the column that is used is the name of whats in the location section of the visual?

Thanks a lot!

[Greg_Deckler]

@nolos You need to unpivot your location columns in Power Query Editor. Then this is trivial.

[nolos]

@Greg_Deckler 

Thanks for the fast reply.  I hope i'm right in thinking you are referring to each of the location columns in the Hours Table.  If so, the problem there is that this is a table I created in power BI.  I used measures to add the columns and so that table doesn't show in the Transform Data section.  Or am I doing it wrong?

Thanks again!

[Greg_Deckler]

@nolos Well, there is a DAX Unpivot but it isn't pretty. I would have to understand your situation better but in general it's not possibly or at least easy to do what you are trying to do with the data formatted that way. You would have to do something like:

Measure = 
  SWITCH( 'Table'[location],
    "Appleton", SUMX( SELECTCOLUMNS( 'Data', "__Value", [Appleton] ), [__Value] ),
    "Boonville", SUMX( SELECTCOLUMNS( 'Data', "__Value", [Boonville] ), [__Value] ),
    "Butler", SUMX( SELECTCOLUMNS( 'Data', "__Value", [Butler] ), [__Value] ),
   ...
  )

DAX Unpivot - Microsoft Fabric Community

[nolos]

@Greg_Deckler 

This may seem silly but is there any way to do something like this...but actually working?

Location1 = SELECTEDVALUE(Locations[location])

hrs = SUMX(Hours,[Location1])

or

hrs =
var loc = SELECTEDVALUE(Locations[location])
var tbl = CONCATENATE("Hours", CONCATENATE("[",CONCATENATE(loc,"]")))
return tbl

then drop the below measure in the visual?

avg = SUMX(Hours, [hrs])

I know neither of these work but I was hoping it migh spark a viable solution.

Thanks.