Help with Summarize - related tables not being returned

[Dayna]

Hello,

I need some assistance with SUMMARIZE, I have a table that I've split out to avoid MANY-TO-MANY relationships, and I need to multiply the value lotion_usage from the table Water Usage, to that of quantity_loc from RCT-WO table.

I thought I could do: SUMMARIZE('Product Master', water usage'lotion_usage', RCT-WO'quantity_loc') but when I use Product Master as my table, it doesn't show any of the related tables to it. If I start from RCT-WO, then it shows Product Master, just not the other way round.

I'm not quite sure what's wrong with this, and would appreciate any assistance you can offer.

Here's the table set-up:

Many thanks,

Dayna

[Dayna]

Hi @amitchandak 

I may be missing something, but I don't get the option when going from this table:

Can you advise?

Thanks,

Dayna

[PaulDBrown]

The option is missing because 'Product Master' is a dimension table, not one of the fact tables. What calculation are you trying to use SUMMARIZE for?

[Dayna]

@PaulDBrown I didn't realise it could recognise that! How does it recognise what is a dimension table and what is a fact table?

The calculation I want to do is avg(water_value) * sum(tr_qty_loc), which is fine as a measure per row, but not for the total. 

Many thanks,

Dayna

[PaulDBrown · Accepted solution]

I didn't realise it could recognise that! How does it recognise what is a dimension table and what is a fact table?

SUMMARIZE allows to reference related tables through the relationships (expanded table concept). A dimension table (generally) doesn't have "related tables" in that sense

Try:
Calc = SUMX('Product Master', [avg water_value] * [sum tr_qty_loc])

[Dayna]

Perfect, thank you!

Can you explain why that worked compared to doing a summarize just so I know more for future, please?

[PaulDBrown]

SUMX is an iterator, so it performs the implicit calculation [avg water_value] * [sum tr_qty_loc] for each row in the Product Master table and then sums up the values. Since Product Master is a dimension table for the tables for both [avg water_value] and [sum tr_qty_loc], it provides the filter context for the calculation.

You would need SUMMARIZE if you needed to calculate a value for example using fields from 2 dimension tables which may not necessarily be present in the actual visual to provide the filter context. SUMMARIZE is then used to create a virtual table to provide the filter context needed for the calculation. This is one example. There are many uses for SUMMARIZE, but not in your particular case, since the table you need to provide the filter context is Product Master

As for why SUMMARIZE doesn't "see" related fields in "dimension" tables, I suggest you check out  this article from the gurus at SQLBI

[Dayna]

Thank you @PaulDBrown 

[v-easonf-msft]

Hi, @Dayna 

What fields are these three tables based on to establish the relationship between the tables?
I suspect it may be because one lotion_usage in the table Water Usage corresponds to multiple quantity_locs in the RCT-WO table.

Best Regards,
Community Support Team _ Eason

[Dayna]

Hi @v-easonf-msft 

Exactly that, I was going to go from the joining table for Product Master, and summarize the data from Water Usage (Components) for water_value and also from RCT-WO for tr_qty_loc. 

Many thanks,

Dayna

[amitchandak]

@Dayna , When you a One side table (Dimension), You can only use measures from Many side Tables(Facts)

When you use many size table name in summarize, then you can use dimension column also in grouping

example

Summarize(Fact, Dim[Col1], Dim2[Col2])