Multiply DirectQuery row value with imported table value (DAX)

[Anonymous]

In a composite model (mixed storage mode) I have a large fact table using DirectQuery (I am not able to change this source) and a small imported table which have a many-to-one relationship on "product_id". The fact table contains the units sold (by product_id, date, location, etc.), while the local table contains the product weight (by product_id).

What I am looking to accomplish seems quite simple:

1. Multiply the units sold (fact table) for with the corresponding product weight (local table).
2. Create a measure that sums all of these to a "total weight" (that can be sliced if needed).

I have looked into the calculated column approach (however functions like RELATED or LOOKUPVALUE do not work on the DirectQuery table) and using a measure. For the latter, a simple measure like SUM('fact'[units sold]) * MAX('dim Product'[weight]) works for a single "product_id" due to the relationship set, but totals and subtotals come out wrong. Adding SUMX over the 'fact' table to this for calculations at row level still seems to give me incorrect totals.

How can I workaround the restrictions of DirectQuery for this seemingly simple requirement?

[Applicable88]

@Anonymous I'm not 100% sure about your mentioned data, but given that one thing did return you the right result for one single product ID, you might try the following.

If your measure is working for one single product ID like you mentioned:
Measure= SUM('fact'[units sold]) * MAX('dim Product'[weight]) 

Since there is restrictions because of the different import variations try using x-aggregated function to get right totals:

Measure2 = sumx ('Facttable', [Measure])

[Anonymous]

@Applicable88 Thanks for thinking along, I did mention in the opening post that I had tried that. However, SUMX('fact', SUM('fact'[units sold]) * MAX('dim Product'[weight])) gives for totals the units sold (total) multiplied by the maximum of all product weights. I.e. it still doesn't seem to do the calculations at row level. Does something need to be added to the measure to "force" calculations for each row, or are there limitations due to mixed storage model?

[Applicable88]

@Anonymous yes.  You should not put the function in, but the Measure. In every measure there there is actually a invisible "calculate" function wrapped around it. But you are calculating everything from the table. You can either try it with two measures like I explained above or you can wrap the calculate function within the "expression" part of SUMX. Like this:

SUMX('fact', calculate (SUM('fact'[units sold]) * MAX('dim Product'[weight])) )

Hope that helps.

[Anonymous]

@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?

[Anonymous]

@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?

[Applicable88 · Accepted solution]

@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.

[Anonymous]

@Applicable88 Thanks for your help & fast response on this, very much appreciated! 

[Applicable88]

Hi @Anonymous, 

I don't know for sure why. But maybe we can try another approach. Since you have a many to 1 relationship lets try step by step to be sure whats happening here. 

We now using related function. The related function need to be used from the many side of the table:

Measure : sumx ( 'Facttable','Facttable' [unitssold] * related ( 'dimproduct'[weight])

I now would think you already have the right result for row and as total. Please let me know if it works out for you.

Best. 

[Anonymous]

@Applicable88  When I try RELATED (this only takes one column as input, correct?) it does find 'dim Product'[weight] as an option. But when I hit enter it tells me:

The column 'dim Product[weight]' either doesn't exist or doesn't have a relationship to any table available in the current context.

From my limited understanding I thought this is due to limitations on the RELATED function when using mixed storage model.