Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ash187
Frequent Visitor

Many to Many relationship Column Access Issue

Hi, I have two tables, a site specific sales table, Table1, with date, product ID and QtySold (product quantity). The other table, Table2, is an overall sales record table of TotQty (total quanity) per month and year. Here is a snapshot of the data in excel files below

Table1

Date                      ProdID  QtySold   Year

 

1/4/2018382018
1/10/20181172018

..

2/5/2018342018
2/9/20182162018

...

3/1/2019492019
3/1/2019342019

 

and the second table, Table2

YearMonthTotQty

2018January5000
2018February4000
2018March7000
2019January900
2019February800
2019March700

 

Due to the nature of the table, when I connect Year to Year, it's a many to many connection. What I would like to draw is the site specific sales quantity divided by total quantity vs the three months. I am able to view year specific data using a year slicer. Independently the bar charts for site specifc QtySold  and total sales quantity TotQty were easily drawn. But how can I use RELATED function to plot the quantity sold to total quantity ratio ( i.e. the site specific QtySold in Table1 divided by total sales quantity in Table2) vs the three months for that year ? 

 

 

1 ACCEPTED SOLUTION
Arjunarao
Resolver I
Resolver I

You can achive your requirement like this.
Create a Calendar and design the data model as shown.

Download the file from here: https://drive.google.com/open?id=15DYzOIHn0-uTjCjFKSHGcSVoIUgayAEn

 

Calendar =
VAR MinYear = YEAR ( MIN ( Table1[Date] ) )
VAR MaxYear = YEAR ( MAX ( Table1[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)

1.JPG

2.JPG

 

View solution in original post

3 REPLIES 3
Arjunarao
Resolver I
Resolver I

You can achive your requirement like this.
Create a Calendar and design the data model as shown.

Download the file from here: https://drive.google.com/open?id=15DYzOIHn0-uTjCjFKSHGcSVoIUgayAEn

 

Calendar =
VAR MinYear = YEAR ( MIN ( Table1[Date] ) )
VAR MaxYear = YEAR ( MAX ( Table1[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)

1.JPG

2.JPG

 

Ashish_Mathur
Super User
Super User

Hi,

What you should be ideally be doing is this:

  1. In Table2, create a Date column by writing the following calculated column formula: Date=1*("1/"&Table2[Month]&"/"&Table2[Year])
  2. Create a Calendar Table
  3. Build a relationship from the Date column of Table1 and Table2 to the Date column of the Calendar Table
  4. In the Calendar Table, write these calculated column formulas to extract Year and Month: Year = Year(Calendar[Date]) and Month = FORMAR(Caloendar[Date],"mmmm")
  5. To your visual, drag Year and Month from the Calendar Table

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
itchyeyeballs
Impactful Individual
Impactful Individual

I'd look at creating a third table with the years in it to act as a common dimension table rather than connect the two fact tables.

This link has good description https://powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.