Call 0700ANALYTICS, +234-808-938-2423, +1-941-312-2149 or Mail: info@UrBizEdge.com or Visit: www.urbizedge.com | Privacy Policy | Terms and Conditions

# Register for the next special Business Data Analysis and In-depth Excel Training

## Sunday, April 21, 2019

### 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?

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!

Data Science Course

2. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.

Simple Linear Regression

Correlation vs Covariance

Correlation vs Covariance
Simple linear regression
data science interview questions

4. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
machine learning course training in guduvanchery

5. What an extremely wonderful post this is. Genuinely, perhaps the best post I've at any point seen to find in as long as I can remember. Goodness, simply keep it up. 360DigiTMG

6. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
visit here

7. incredible article!! sharing these kind of articles is the decent one and I trust you will share an article on information science.By giving an organization like 360DigiTMG.it is one the best foundation for doing guaranteed courses

hrdf claimable training

8. Incredibly conventional blog and articles. I am realy very happy to visit your blog. Directly I am found which I truly need. Thankful to you and keeping it together for your new post.
https://360digitmg.com/course/certification-program-in-supply-chain-analytics

9. I think I have never watched such online diaries ever that has absolute things with all nuances which I need. So thoughtfully update this ever for us.
data science malaysia

10. I think I have never watched such online diaries ever that has absolute things with all nuances which I need. So thoughtfully update this ever for us.
difference between analysis and analytics

11. incredible article!! sharing these kind of articles is the decent one and I trust you will share an article on information science.By giving an organization like 360DigiTMG.it is one the best foundation for doing guaranteed courses
data science course noida

12. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.