Forum Discussion
Measure from non related Table Always null
Your example data does suggest the DateKey granularity is not the same, but maybe that is just a typo. In any case, because you have two fact tables, you are not able to use SUMMARIZE on just one of them. You could try a CROSSJOIN expression like this:
COGS =
SUMX (
CROSSJOIN ( VALUES ( Calendars[DateKey] ), VALUES ( Product[LCBO SKU] ) ),
[sum91] * [total shipment Cost of Goods Sold]
)
Pat
- Anonymous5 years agoNot applicable
Hi mahoneypat thank you again for helping me 🙂
I think i'm going to the right direction,
Here is the result of my calculation:
OLD_COGS here is a simple :
[sum9l] * [total shipment Cost of Goods Sold]CrossjoinCOGS is :
So basically I put all related table from both fact tables in the crossjoin section, is that correct ?
CrossjoinCOGS = SUMX ( CROSSJOIN ( VALUES ( 'Product Master'[Product Key] ), VALUES ( 'Product LCBO'[Lcbo_Product_Key] ), VALUES ( 'Channel Finance'[Channel Code] ), VALUES ( Calendars[Date Key] ) ), [sum91] * [total shipment Cost of Goods Sold] )And summarizeCOGS is
Basically I put all related column from both fact tables, same questions is that correct ?
SummarizeCOGS = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Financials', 'Product LCBO'[LCBO SKU No], 'Channel Finance'[Channel Code], 'Product Master'[Product Key], 'Calendars'[Date Key] ), "COGS", [sum91] * [total shipment Cost of Goods Sold] ), [COGS] )I'm happy, because i feel like I have the same result with both calculation, however an other question come to my mind :
- Is this result being lower than the initial one is because we take all values that we have in common ? just like a innerjoin in SQL right ?
- In terms of performance, what calculation should I use ?
Thank you so much again mahoneypat & PaulDBrown for answering
- mahoneypat5 years ago
Microsoft Employee
I wasn't sure if your two measures use columns in both fact tables or not. If the SUMMARIZE one gives the expected values, I would expect it to be more performant. However, since you have both written, I would try them separately in your visual and view the results with Performance Analyzer (or just try them both out qualitatively) and see which one is faster.
Pat