Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables:
Table 1 is Called "Data"
I have a second Table called "Lookup"
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.
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.
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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |