cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 1
Community Support Team
Community Support Team

Re: join two summarize by date

Hi jflanner,

 

Please check if the data type of [Date] columns in table 'Nutrition-Summary' and table 'Exercise-Summary' are same.

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors