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
Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
ReplyDeleteCorrelation vs Covariance
Simple linear regression
data science interview questions
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
Great Article Artificial Intelligence Projects
ReplyDeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai Project Centers in Chennai