Calendar table relationships problem

[Razik_BNPL]

I have this calendar table

Calendar =

var MinDate = MIN(Final[Submit Date and Time])

Var MaxDate = Max(Final[Submit Date and Time])

VAR Days = CALENDAR (MinDate , MaxDate )

RETURN ADDCOLUMNS (

    Days,

    "Year", YEAR ( [Date] ),

    "Month Number", MONTH ( [Date] ),

    "Month", FORMAT ( [Date], "mmmm" ),

    "Year Month Number", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,

    "Year Month", FORMAT ( [Date], "mmm yy" ),

    "Week Number", WEEKNUM ( [Date] ),

"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),

"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] ),

"Is Working Day",not WEEKDAY([Date]) in {1,7}

)

i created a connection between calendar and other table, but the table is not connected. selected month from the calendar and Avg from the report table. 

[123abc]

It seems like you've created a calendar table and are attempting to establish a relationship between the calendar table and another table in Power BI or a similar data modeling tool. If the tables are not connecting as expected, here are some common troubleshooting steps:

1. Check Data Types and Formats: Ensure that the date columns in both tables have the same data types and formats. They should be of type 'Date' and should have the same format (e.g., 'MM/DD/YYYY' or 'YYYY-MM-DD').

2. Relationship Type: Verify the type of relationship you've established between the tables. In Power BI, you can create either a single-directional or bidirectional relationship. Make sure you've chosen the appropriate type based on your reporting needs.

3. Column Names: Double-check that the columns used for the relationship have identical names in both tables. In your case, you're using a calendar table, so make sure the date column names match in both tables.

4. Filter Direction: Understand how filtering works in your scenario. By default, relationships filter the 'Many' side by the 'One' side. If your report table is the 'Many' side, you may need to adjust the filter direction.

5. Inactive Relationships: If you have multiple relationships between the tables, ensure that the correct relationship is marked as active. Power BI allows you to define multiple relationships between tables, but only one can be active at a time.

6. Date Hierarchy: Power BI might automatically create a date hierarchy in your calendar table. This hierarchy can sometimes affect filtering. You can disable or adjust this hierarchy in the 'Modeling' tab.

7. Cross Filtering: Check if cross-filtering is enabled on both sides of the relationship. Cross-filtering allows filters to propagate in both directions.

8. Refresh Data: Sometimes, the issue can be resolved by refreshing the data model. Ensure your data is up to date and that the relationships are correctly established after each data refresh.

9. DAX Measures: Verify that the DAX measures you're using in your visuals are correctly referencing the related tables and columns.

10. Errors and Warnings: Look for any error messages or warnings in the Power BI Query Editor or the Relationships diagram. These can provide insights into what might be causing the issue.

11. Data Sampling: Consider sampling a small subset of your data for testing. Sometimes, issues can be related to the volume of data or specific data entries.

By following these troubleshooting steps, you should be able to identify and resolve the issue with your table relationships. If you're still facing problems, please provide more specific details about the issue or any error messages you encounter, and I can offer further assistance.

[Ritaf1983]

Hi @Razik_BNPL 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Razik_BNPL]

@Ritaf1983 @123abc noted with thanks. can you pls help on this now?

calendar table

Date	Year	Month no	Month
1/3/2022 12:00:00 AM	2022	1	January

Table 1

name	submit date and time	rating	langauge
Sams	7/12/2021 9:37:39 AM	1	English

[v-cgao-msft · Accepted solution]

Hi @Razik_BNPL ,

The date column in the "Final" table has a timestamp, so you need to create a separate date column and use that date column to create a relationship with the calendar table.

Date =
DATE ( YEAR ( 'Final'[Submit Date and Time] ), MONTH ( 'Final'[Submit Date and Time] ), DAY ( 'Final'[Submit Date and Time] ) )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum