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?

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

10 comments:

  1. wonderful article. 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 resolved my all queries.
    Data Science Course

    ReplyDelete
  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

    ReplyDelete
  3. 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.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete

  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete