Forum Discussion
Measure from non related Table Always null
Microsoft EmployeeYour 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
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
- mahoneypat
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