Hi folks,
I’ve been working with TE3 and built the query below.
When I run it, TE3 gives me an error “The TOPN function expects a table expression for argument ‘2’, but a string or numeric expression was used.”
I wasn’t using TOPN, so just for giggles, I removed the evaluate statement and then created “TestMeasure” in my pbix. I dropped TestMeasure onto a card and it returned the correct answer. I then attempted to put the measure into a table visual and I get “Can’t display the visual”. Details behind that are in the attached file.
error-details.mcode (28.6 KB)
Any ideas?
/* START QUERY BUILDER */
EVALUATE
//VAR WOperRev = [Weight - Operating Revenue]
//VAR WOperExp = [Weight - Operating Expense]
//VAR WOperInc = [Weight - Operating Income]
VAR weights =
SUMMARIZECOLUMNS(
soi[Level 03],
KEEPFILTERS( TREATAS( { 1 }, captions[Sub Total] ) ),
KEEPFILTERS( TREATAS( { 0 }, calendar[Month Offset] ) ),
KEEPFILTERS( TREATAS( { 40 }, captions[Caption ID] ) ),
KEEPFILTERS( TREATAS( { 8 }, cost_centers[Reporting Company] ) ),
"WOperRev", [Weight - Operating Revenue],
"WOperExp", [Weight - Operating Expense],
"WOperInc", [Weight - Operating Income]
)
VAR WOperRev = MAXX( weights, [WOperRev] )
VAR WOperExp = MAXX( weights, [WOperExp] )
VAR WOperInc = MAXX( weights, [WOperInc] )
VAR weightTable =
ADDCOLUMNS(
SUMMARIZECOLUMNS(
soi[Level 03],
cost_centers[Reporting Company],
cost_centers[Cost Center],
chart_of_accounts[Account ID],
captions[Caption ID],
//captions[Caption],
KEEPFILTERS( TREATAS( { 1 }, captions[Sub Total] ) ),
KEEPFILTERS( TREATAS( { 0 }, calendar[Month Offset] ) ),
KEEPFILTERS( TREATAS( { 40 }, captions[Caption ID] ) ),
KEEPFILTERS( TREATAS( { 8 }, cost_centers[Reporting Company] ) ),
"SubSum", [Subsys03_Sum],
"BudSum", [Budgets03_Sum]
),
"BudWRev", [BudSum] * WOperRev,
"BudWExo", [BudSum] * WOperExp,
"BudWInc", [BudSum] * WOperInc,
"WRev", WOperRev,
"WExp", WOperExp,
"WInc", WOperInc
)
VAR AvgRev = AVERAGEX( weightTable, [BudWRev] )
RETURN
AvgRev