Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ahjamil
Frequent Visitor

Dynamic Column Name

I have a table called "Cost" that has Items and Monthly Costs:           

ItemCost YearJan_Cost   Feb_Cost  Mar_Cost  Apr_Cost May_Cost
A20221010111214
B20221415151617
C20221719212019

Currently I have a measure (There are a lot of these measures and I need them for every Product):

SumCost = sumx(filter(cost, Cost[Cost YEAR]=2022), Cost[Jan_Cost])

I have to change the measure to dynamically pick up the column, so that I only change one value every month and all my measures start picking up the required column like:
Var ColName = "Jan_Cost"
SumCost = sumx(filter(cost, Cost[Cost YEAR]=2022), Cost[ColName])

Any help will be appreciated. 

1 REPLY 1
AlexisOlson
Super User
Super User

It's almost always a better option in these sorts of situations to unpivot your month columns so that your data looks like this instead:

AlexisOlson_0-1659562474857.png

 

Then SumCost is just SUM ( Cost[Cost] ).

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors