Forum Discussion
Multiply DirectQuery row value with imported table value (DAX)
Anonymous I would think the summarize doing its job, what would be the job of related function in a "normal" setting without direct query.
Summarize is doing something like what group by is doing in SQL. Its grouping/aggregating where are many product id (facttable) to the table where is only one productId towards the right weight of that productID.
Thats also why you didnt got the right totals before. You were using the max weight of the entire table instead of the right weight of the filter context.
Applicable88 Thanks, that definitely helps me to understand and points me in a clearer direction.
I've tried just now (with two separate measures), I am getting a very strange error if I bring the final measure in a visual:
OLE DB or ODBC error: Query (8, 2) The column 'PC Group (groups)' specified in the 'SUMMARIZE' function was not found in the input table..
The strange part is that the measure does not contain SUMMARIZE, moreover PC Group is a completely separate dimension (part of the DirectQuery dataset, with only a relationship to the fact table). I understand this seems to go beyond the initial question, but any thoughts on why a measure containing SUMX can generate the above error in a visual?
Applicable88 I think I found a solution! I went back to the first idea and since it was giving me a (seemingly unrelated) "SUMMARIZE" error, I just tried wrapping the 'fact' table in a SUMMARIZE function as follows:
TempMeasure = SUM('fact'[units sold]) * MAX('dim Product'[weight])
FinalMeasure = SUMX(SUMMARIZE('fact', 'fact'[product_id]), [TempMeasure])
This seems to give correct totals. Before closing the topic, do you have any idea why the additional SUMMARIZE within the SUMX would make sense?
Anonymous I would think the summarize doing its job, what would be the job of related function in a "normal" setting without direct query.
Summarize is doing something like what group by is doing in SQL. Its grouping/aggregating where are many product id (facttable) to the table where is only one productId towards the right weight of that productID.
Thats also why you didnt got the right totals before. You were using the max weight of the entire table instead of the right weight of the filter context.
Applicable88 Thanks for your help & fast response on this, very much appreciated!