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!

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

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
data science course noida

8. The Business Data Analysis and In-depth Excel Training offered by Urbizedge seems like a valuable opportunity for professionals seeking to enhance their skills in data analysis and Excel. With the increasing importance of data-driven decision-making in the business world, this training can provide participants with the knowledge and tools necessary to excel in their roles.UK Dissertation Writing

9. I'm thrilled to register for the upcoming Business Data Analysis and In-depth Excel Training! As a student eager to dive into the world of data analytics, this opportunity promises to equip me with invaluable skills for my future career. I'm particularly excited about gaining hands-on experience and mastering advanced Excel techniques. Additionally, I'm considering leveraging an assignment writing service Ireland to ensure I can balance coursework effectively alongside this intensive training. Ready to excel in both theory and practice!