Forum Discussion
join two summarize by date
Folks:
I am building an Exercise & Nutrition solution in power bi desktop. (I am getting data from myfitnesspal.com) The data comes in csv form. The csvs have been pulled into the solution and are in my data model. I also have a date dimension table where each date is a single row.
There are csvs (thus tables in my data model) for nutrition and exercise. These are 1:m with the date table. (You can exercise more then once per day - and you can eat more then once per day.) The relationships have been set up in the model.
My goal now is to build a calculated table that is a summary of calories. What I have is:
Calories =
NATURALINNERJOIN
(
SUMMARIZE
(
'Nutrition-Summary',
'Nutrition-Summary'[Date],
"FoodCalories",SUM('Nutrition-Summary'[Calories]),
"FoodCarbs",SUM('Nutrition-Summary'[Carbohydrates (g)])
),
SUMMARIZE
(
'Exercise-Summary',
'Exercise-Summary'[Date],
"ExerciseCalories",SUM('Exercise-Summary'[Calories])
)
)
Each of those SUMMARIZE statements work if executed alone. But when I go to join them together (as you would two CTEs in SQL) I get "No common jopin columns detected. The join function NATURALINNERJOIN requires at-least one common join column." I want these to join on date. How do I make that happen?
1 Reply
- v-yuta-msftCommunity Support
Hi jflanner,
Please check if the data type of [Date] columns in table 'Nutrition-Summary' and table 'Exercise-Summary' are same.
Regards,
Jimmy Tao