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
|
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
And to join our training class, visit https://www.urbizedge.com/powerbi
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
ReplyDeletemachine learning course training in guduvanchery
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
ReplyDeleteI 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.
ReplyDeletevisit here
ReplyDeleteincredible 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
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.
ReplyDeletehttps://360digitmg.com/course/certification-program-in-supply-chain-analytics
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.
ReplyDeletedata science malaysia
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
ReplyDeletedata science course noida
ReplyDelete
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
ReplyDelete
ReplyDeleteI'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!