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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Julier
Helper II
Helper II

Relationships cardinality

Hello i am trying to link the two tables below by account id, but keep getting them message regarding cardinality and i am unable to find a link that works to allow me to build a combination chart showings sales against forecast

Julier_0-1713092085840.png

 

1 ACCEPTED SOLUTION

Thanks for this, I'm sure that this works but I  am still unable to undersatnd how to do it thats just my weakness with power bi, i will give up on trying to do this but will accept that this is probably the solution.

View solution in original post

12 REPLIES 12
Julier
Helper II
Helper II

 have added the calendar but no luck, I have added  a chart which shows SQM for each site by date but i needed to add the monthly S&OP for each site i have tried everything but no luck 

Julier_0-1713618789746.png

 

In your S&OP table create a fake date column (for example first day of the month).  Then join the tables via the date.

Hi,  i have done this and i can bring in the S&OP but its the same figure for each and every account ID, it does not change as it should?

Julier_0-1713965794664.pngJulier_1-1713965853822.png

 

I have made the change and the chart now looks as below and still does not change when another customer is selected as shown below.

Julier_1-1713966849450.png

Julier_2-1713967000372.png

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Below is what i am trying to replicate with some sample data below.

Julier_1-1714051023846.png

 

 

ALL SITE

    
SITE_IDACCOUNT_IDCustomer NameDateSQM
LOC2000099ABCDEF01/04/202424419
LOC2000099ABCDEF15/04/20245814
BEL700373HIJJJJ25/04/202425256
BEL700373KNNNN30/04/202431524
BUR1010566XXXXXX10/04/202427054
BUR1010566XXXXXX18/04/20243251
FEA101021ZZZZZZ06/04/202415362
     
     
S&OP    
ACCOUNT_IDMonth NameSQMSITE_ID 
2128April825000LIV 
200976April35000FEA 
2775April26486LIV 
600146April4783BUR 
2002403April25200LOC 
600079April35000BUR 
600139April400000BUR 
     
CALENDER   
DateYearMonthMonth Name 
########20244April 
########20244April 
########20244April 
########20244April 

Add the date column:

lbendlin_1-1714565823316.png

Create the calendar and wire it in:

 

Calendar = ADDCOLUMNS(CALENDAR("2024-04-01",TODAY()),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmm"),"MNo",FORMAT([Date],"mm"))

 

lbendlin_0-1714565762033.png

 

Then you can base your visuals x axis on the calendar.

Thanks for this, I'm sure that this works but I  am still unable to undersatnd how to do it thats just my weakness with power bi, i will give up on trying to do this but will accept that this is probably the solution.

Make sure to use the date from the calendar table in the x axis.

Julier
Helper II
Helper II

Hello, thank you, have trried this but no luck is there anyrhing else that i must have missed?

Julier_0-1713164326335.png

 

@Julier  as suggested by @lbendlin  you need to use calender table to join them. 

Calendar = CALENDAR(MIN('YourStartDateColumn'), MAX('YourEndDateColumn'))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

lbendlin
Super User
Super User

You are missing the Calendar table that acts as a controlling dimension for your two independent fact tables. Don't link fact tables with each other.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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