Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Addition of columns from another table

Hi all,

 

I have a few tables that looks like this:

Table 1

Date 1Value 1
1/1/202034
1/2/202076
1/3/202046
1/4/202084

 

Table 2

Date 2Value 2
1/3/202055
1/4/202035
1/5/202057
1/6/202025
1/7/202075

 

Ideally, I would like a table that looks omething like this: 

MonthDateValue 1Value 2
January1/1/202034 
February1/2/202076 
March1/3/20204655
April1/4/20208435
May1/5/2020 57
June1/6/2020 25
July1/7/2020 75

 

Is there a way for me to do this using DAX or in Query? Any help would be appreciated. Thank you!

2 ACCEPTED SOLUTIONS
CNENFRNL
Community Champion
Community Champion

Hi, @Anonymous , you can first merge the 2 data tables by date column by join kind of "Full Outer" in Power Query,

Untitled.png

then tweak the data per your requirements.

 

You might want to refer to the attach file for details.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

Hi @Anonymous ,

 

Create a calendar table and create 1 - * or 1-1 relationships between the other two tables. Use the date field in the table visual.

V-lianl-msft_0-1606210558630.pngV-lianl-msft_1-1606210582438.png

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

Hi, @Anonymous , you can first merge the 2 data tables by date column by join kind of "Full Outer" in Power Query,

Untitled.png

then tweak the data per your requirements.

 

You might want to refer to the attach file for details.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Hi, will this work for live data that updates frequently? Thank you.

@Anonymous , Please find the attached file after the signature. In case it live connection have date table at source.

 

 

Anonymous
Not applicable

Hi it seems like I do not have the latest ver. of power bi and cant seem to download it

 

Hi @Anonymous ,

 

Create a calendar table and create 1 - * or 1-1 relationships between the other two tables. Use the date field in the table visual.

V-lianl-msft_0-1606210558630.pngV-lianl-msft_1-1606210582438.png

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , Create a date table and join on the date of both table. And create a visual using the date from the date table .

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Anonymous
Not applicable

Hi what do you mean by join on the date of both table and create a visual using the date from the date table? Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.