cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bglibkowski123a Frequent Visitor
Frequent Visitor

use a value in one column in a second column formula in Dax

I have two tables: 

 

Table 1 is Called "Data"

 

Data Table.PNG

 

I have a second Table called "Lookup"

 

Lookup.PNG

 

My question: How do I replace the "[V2]" in this formmula "DataColumn Avg = AVERAGEX(Data,Data[V2])" with a "conditional variable" that allows the value in the Variable row to "conditionally determine which Row in the Data Table is called to be averaged"?  In other words, in row 1 I want V1 to be in the formula ("DataColumn Avg = AVERAGEX(Data,Data[V1])") and in row 2 I want V2 to be in teh formula ("DataColumn Avg = AVERAGEX(Data,Data[V2])".

 

I can't figure out how to write this formula.

 

Thanks in advance for any assistance.

2 REPLIES 2
bglibkowski123a Frequent Visitor
Frequent Visitor

Re: use a value in one column in a second column formula in Dax

I had a typo "Row" reference should be "column" correction below.  Also cleaned up some other confusing language in the question....

 

XXXXXXXXXX Corrected Question Text XXXXXXXXXXXXXX

 

My question: How do I replace the "[V2]" in this formula "DataColumn Avg = AVERAGEX(Data,Data[V2])" with a "conditional variable" that allows the value in a given row of the the Variable column to "conditionally determine which Column in the Data Table is called to be averaged"?  In other words, in row 1 I want V1 to be in the formula ("DataColumn Avg = AVERAGEX(Data,Data[V1])") and in row 2 I want V2 to be in the formula ("DataColumn Avg = AVERAGEX(Data,Data[V2])".

 

I can't figure out how to write this formula.

 

Thanks in advance for any assistance.

Super User
Super User

Re: use a value in one column in a second column formula in Dax

Hey,

 

unfortunately there is no DAX formula that is able to translate into an object reference, for this reason you have to use something like this

 

DataColumn Avg = 
SWITCH(
  TRUE()
  ,'LookupTable'[LookupColumn] = "V1", AVERAGEX(Data,Data[V1])
  ,'LookupTable'[LookupColumn] = "V2", AVERAGEX(Data,Data[V2])
)

 

Another approach could also be to transpose your data table into Key, Value pairs during the import, then use the value from the lookup table to filter on "V1". This means create a data table that looks like this

KEY | Value

V1 | 1

V1 | 3

...

V2 | 2

...

 

Hopefully this gets you started

 

Regards

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 361 members 3,553 guests
Please welcome our newest community members: