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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nrichards_au
Frequent Visitor

Create Dax Table using Many to Many

I have two tables that unfortunately are not in a database environment or this would be simple and I'd exploit a cartesian product to get what I need

 

T1
Year|Region|Growth

2023|A|50
2023|B|80
....

2024|A|100
2024|B|200

T2
Year|Week|WK_ID
2023|1|202301
2023|2|202302
....

2024|51|202451

2024|52|202452

 

Many:Many on the Year field

 

Desired Output

WKID|Region|Growth
202301|A|50

202302|A|50
202303|A|50

Any ideas?

1 ACCEPTED SOLUTION
Wilson_
Memorable Member
Memorable Member

Hello nrichards_au,

 

Can you use power query to do this instead of DAX? If so, you should be able to merge the two tables, joining on the year column.


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

View solution in original post

2 REPLIES 2
Wilson_
Memorable Member
Memorable Member

Hello nrichards_au,

 

Can you use power query to do this instead of DAX? If so, you should be able to merge the two tables, joining on the year column.


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Just implemented this about 15 mins prior to your post. I had totally forgotten about the join functions of PQ and happened to be in the load editor and saw the icon.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors