Relationships cardinality

[Julier]

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]

 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 

[lbendlin]

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

[Julier]

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]

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.

[lbendlin]

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...

[Julier]

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

ALL SITE
SITE_ID	ACCOUNT_ID	Customer Name	Date	SQM
LOC	2000099	ABCDEF	01/04/2024	24419
LOC	2000099	ABCDEF	15/04/2024	5814
BEL	700373	HIJJJJ	25/04/2024	25256
BEL	700373	KNNNN	30/04/2024	31524
BUR	1010566	XXXXXX	10/04/2024	27054
BUR	1010566	XXXXXX	18/04/2024	3251
FEA	101021	ZZZZZZ	06/04/2024	15362
S&OP
ACCOUNT_ID	Month Name	SQM	SITE_ID
2128	April	825000	LIV
200976	April	35000	FEA
2775	April	26486	LIV
600146	April	4783	BUR
2002403	April	25200	LOC
600079	April	35000	BUR
600139	April	400000	BUR
CALENDER
Date	Year	Month	Month Name
########	2024	4	April
########	2024	4	April
########	2024	4	April
########	2024	4	April

[lbendlin]

Add the date column:

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"))

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

[Julier · 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.

[lbendlin]

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

[Julier]

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

[johnbasha33]

@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]

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.