Power BI DAX #1: The Magic of CROSSJOIN

Last week I facilitated a training where the participants were staff of Nigeria Bottling Company (makers of Coca-Cola in Nigeria). After my usual prepared practice, we delved into making the types of reports they need. 

One of the reports had a very interesting twist that CROSSJOIN was perfect for. For confidentiality, I won't tell you the exact situation we had regarding the said report but I have created a simpler and easier to relate with example that I will use to show you the magic of CROSSJOIN.

CROSSJOIN is a DAX formula that generates the cartesian product of all the rows in tables inputted into the formula argument. You can go through Microsoft's official documentation on it at https://docs.microsoft.com/en-us/dax/crossjoin-function-dax 

CASE STUDY
ABC Limited has five branches across Nigeria and sells four products. Each product has same target across all the branches.

Branch	Manager
Lagos	Jide Aje
Abuja	Ahmed Kazeem
Kaduna	Sanni Eze
Port Harcourt	Joe Opoi
Enugu	Chika Nwabueze

Product	Target
Product A	100
Product B	120
Product C	90
Product D	130

You are to generate a new table that will combine these two tables into one -- having all four products with assigned targets showing for all the five branches.

Branch	Manager	Product	Target
Lagos	Jide Aje	Product C	90
Abuja	Ahmed Kazeem	Product C	90
Kaduna	Sanni Eze	Product C	90
Port Harcourt	Joe Opoi	Product C	90
Enugu	Chika Nwabueze	Product C	90
Lagos	Jide Aje	Product A	100
Abuja	Ahmed Kazeem	Product A	100
Kaduna	Sanni Eze	Product A	100
Port Harcourt	Joe Opoi	Product A	100
Enugu	Chika Nwabueze	Product A	100
Lagos	Jide Aje	Product B	120
Abuja	Ahmed Kazeem	Product B	120
Kaduna	Sanni Eze	Product B	120
Port Harcourt	Joe Opoi	Product B	120
Enugu	Chika Nwabueze	Product B	120
Lagos	Jide Aje	Product D	130
Abuja	Ahmed Kazeem	Product D	130
Kaduna	Sanni Eze	Product D	130
Port Harcourt	Joe Opoi	Product D	130
Enugu	Chika Nwabueze	Product D	130

So how do we achieve this?

ANSWER: CROSSJOIN

If you want to follow along and recreate this tutorial, just launch Power BI. And under Home menu, click on Enter Data. Copy paste or type in the Branch table data.

Repeat same steps for Product table.

And to the magic step: under Modeling menu, click on New Table and type CrossJoin Table = CROSSJOIN('Branch Table','Product Table')

And voila! You are done.

BONUS: What if you needed to do this in Excel?

You will have to use PowerQuery's Unpivot, and maybe more than once if both tables have more than 2 columns of relevant data.

You can watch the YouTube tutorial: https://youtu.be/A_2GM4Ig24k 

Enjoy!

And to join our training class, visit https://www.urbizedge.com/powerbi