Should a summable field be part of a dimension table?

[Kelderic]

As a way to help myself learn more abou Fabric and Power BI, I am trying to convert an Excel file that I've been using to log my food intake and calorie counts into a Power BI report, with the data stored in OneLake.

My question is about data architecture. I am vaguely familiar with dimension and fact tables. But in this case, I'm struggling to figure out how to fit my data into that ideal. Here is a simplified example. I have two tables:

Table 1 - Pantry Item

ItemName (StringType)	Calories (IntegerType)
Apple	200
Slice of Pie	500

Table 2 - Log Entry

Date (Date)	MealName (StringType)	Item (StringType)	Count (FloatType)
4/11/2025	Breakfast	Apple	2
4/11/2025	Lunch	Slice of Pie	1

In Excel, I have a formula column on the Table 2, that does an XLOOKUP to table 1 to get the calories, and that is what I then summarize, per day, per week, etc. But I'm not sure what the best practice would be in a BI environment. Should I transform the data to merge the calories into the Log Entries fact table? Should I keep them in the Pantry Item table? Should I consider Pantry Item to be a fact table?

Any suggestions or recommendations would be appreciated, thank you.

[v-dineshya · Accepted solution]

Hi @Kelderic ,

Thanks for reaching out to the Microsoft fabric community forum. 

No — summable fields like calories should typically not be part of a dimension table in the reporting model. Instead, they should be used in calculations that live in the fact table or in your Power BI measures. That being said, your dimension table can still contain that kind of data, but it should not be the source of aggregation directly.

Suggested Model:
1. Pantry Item (Dimension Table)
This contains metadata about the items.

ItemKey (PK) ItemName CaloriesPerUnit
1 Apple 200
2 Slice of Pie 500

CaloriesPerUnit is not meant to be summed here. It’s an attribute of the item, not a transactional metric.

2. Log Entry (Fact Table)
This is your fact table that logs what was eaten and how many.

Date MealName ItemKey (FK) Count
4/11/2025 Breakfast 1 2
4/11/2025 Lunch 2 1

It links to the Pantry Item table via ItemKey.

The table itself is where you count events — a perfect fact table.

In Power BI:

Use a calculated column or DAX measure to compute calories:

Option A: Add a Calculated Column to Fact Table

Calories = RELATED(Pantry[CaloriesPerUnit]) * FactLog[Count]

Option B: Create a Measure (more flexible)

Total Calories =
SUMX(
FactLog,
FactLog[Count] * RELATED(Pantry[CaloriesPerUnit])
)

This keeps your model normalized and lets you summarize data efficiently by date, item, meal, etc.

Need to Avoid:

Avoid merging calorie data into the fact table permanently during import. That reduces flexibility. What if an item’s calorie count changes (e.g., new brand or updated info)? Keeping that in the dimension table ensures easy updates.

Don’t try to sum CaloriesPerUnit from the dimension. That would give you weird numbers like "700 calories" (200 + 500), which makes no sense unless you know how many servings were eaten.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[Kelderic]

From my research over the past couple days, my understanding is that your option A won't work, due to me using Direct Lake, but option B will work.

You say not to permanently calculate and save multiplied value as a new column on the fact table due to reduced flexability, and the danger in case the calories on the dimension table change, and that makes sense to me. But doesn't a DAX measure that has to be recalculated use extra CUs? IE would the tradeoff of calculating and saving during the silver to gold transform, vs a DAX measure, be that the measure is more flexible, but has to be calculated every time the report is loaded, vs only calculated once? So more CU usage but more flexibility, right?

[lbendlin]

that the measure is more flexible, but has to be calculated every time the report is loaded

not exactly. A measure is calculated every time a report user interacts with a visual that depends on that measure.

[Kelderic]

Thank you for that clarification! Just so I am sure I'm understanding you, when you say "interact" with, if I have that data in a table or other visual, as part of a report. Does opening the report to view it mean that it's been interacted with?

[lbendlin]

you need to be more precise.  "opening the report"  does only trigger the measure if you open that particular report page.  If the measure is linked to a visual on a non-default page then it will only be triggered when the user switches to that page. Pages are not pre-rendered.

[Kelderic]

So opening/viewing the visual will trigger the measure to be calculated.

[lbendlin]

IF the measure is part of the query for the visual. 

This also means that if they user interacts with other parts of the page, or with the filter pane, that potentially the visual will be re-rendered which again requires a new query etc.

[Daniel29195]

@Kelderic 

the pantry item is the dimension table, 

the log entry is the fact table, 

create a relationship between the dimension and fact on the dimension[itemname] --- fact[item] 

and you are good to go .

think of it that way, 

calories of an apple, in your example,  will not change ( not a volatile information that changes everyday )  .  so the calories should be in the dimension table  which is your pantry item table 

hope this makes sense to you .

[Kelderic]

Yes the calories won't change, but the calories x the count from the fact table would be different per log item, and that will have to be summarized.

[Daniel29195]

@Kelderic 

you can use related function  when you want to multiply the calories * count. 

what you can do is to create a calculated column as follow : 

total calories = related ( pantry_item[calories] ) * fact_table[count]) 

[Kelderic]

But calculated columns don't work in Direct Lake, right?

[lbendlin]

The calories are an attribute of the dimension item.  You don't really sum them - you look them up and then use that lookup in the fact calculation. 

There's no need to transform anything but if you are anxious you can use RELATED() to pull that dimension attribute into the Fact table as a calculated column. 

[Kelderic]

That wouldn't work using Direct Lake mode, though, right?

[rosha_rosha]

Hi Kelderic,

Yes, Calories can be in the Pantry Item dimension table as a descriptive attribute (calories per unit), not as a summable measure. The summable value (total calories) is derived in Power BI using a DAX measure (Count × Calories), keeping calculations dynamic and your model normalized.

[Kelderic]

I'm using Direct Lake mode to pull directly from an SQL Endpoint on top of a Lakehouse, and my understanding is that we can't do calculated columns and DAX in Direct Lake mode.

[lbendlin]

I'm using Direct Lake mode to pull directly from an SQL Endpoint

Direct Lake pulls from the Delta lake files.  When you go via the SQL Endpoint you are using Direct Query.

[Kelderic]

Sorry, wasn't clear. I meant DirectLake v1, which uses the SQL Analytics endpoint. But regardless, both versions of DirectLake mode don't allow calculated fields currently, until the new hybrid mode rolls out. So I'd need to do a transformation to take the calories from the pantry table, times the count in the log entry table, as a new column on that log entry table, right, is my current understanding.