Power BI: When I asked you to create common tables: A Quick DAX Solution

Problem Statement: This is a very common scenario when you come up with two more tables and ask for
1. How to model these
2. How to avoid Many to Many joins
3. The best way to analyze this data

Here we have two tables having Item, Cat, date columns, and numeric columns. How do we model this? These tables may not have any join or can have Many to Many Join.

Power Bi Many to Many to Many Join

What I would suggest to you, is to have common tables like Item, cat, and date. Join them with these tables and analyze data together.

Solution- I will suggest a quick DAX solution without creating a numeric key. We will have a blog later, on how to do this with power query and numeric keys.

First, create a date table. I have a few blogs around that. You can just get the code and create one.

Date = var _max = Today() 
var _FYENDMonth =12
var _FYRemain = 12 -_FYENDMonth
var _end = EOMONTH(_max,12-MONTH(_max)+_FYENDMonth)
var _cal = 
ADDCOLUMNS( CALENDAR(DATE(2021,_FYENDMonth+1,01), _end) 
 , "Month Year" , FORMAT([Date], "MMM-YYYY")
 , "Month year Sort" , Year([Date])*100 + month([date])
 , "month start date" , EOMONTH([Date],-1)+1 
 , "month end date" , EOMONTH([Date],0) 
 , "FY year Start Date", if(month([Date])<=_FYENDMonth, EOMONTH([Date],-1*month([Date])- _FYRemain) ,EOMONTH([Date],-1*month([Date])+_FYENDMonth ) )+1 
 , "FY year End Date", if(month([Date])<=_FYENDMonth, EOMONTH([Date],_FYENDMonth-1*month([Date])) ,EOMONTH([Date],12-1*(month([Date])- _FYENDMonth) )) 
 ,"Weekday name", format([date], "dddd")
 , "Weekday", WEEKDAY([Date],2)
 ,"Start Week Date" , [Date] -1* WEEKDAY([Date],2) +1
 ,"End Week Date" , [Date] + 7 -1* WEEKDAY([Date],2) 
 , "Cal Year",year([date]))
return _cal

Now we need the common Item and Cat tables. You can refer to my video how-to that

Create two new tables

Item = DISTINCT(UNION(DISTINCT('Data 1'[Item]),DISTINCT('Data 2'[Item])))
Item = DISTINCT(UNION(DISTINCT('Data 1'[Item]),DISTINCT('Data 2'[Item])))

Now delete the old relation and create the new one. 1-Many single directional join from dimension/Common tables

Power BI Model Without Joins

Make sure, you mark the date table as date table.

Bi-Directional joins can lead to inactive joins

Power BI Inactive Joins

The model with all correct joins (collapsed) — Star Schema

Power BI Star Schema Model

Analyze together

Power BI analyze Star Schema

You can refer video on the same on my YouTube Channel

You can find the file here. Like Share and Comment.

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share.