relationship one-many not working properly

[PowerRookie]

I have a clean report with 1 sql import table. Which has 1 calculated column which is 1 on 1 based on a date-column + 15 years: 
// replacement_date ='sql_replacements'[commencement_date] + (15*365.25)

The formats for al the date columns is date -> shortdate dd/mm/yyyy

The relationship is active. When I create a report based on count ID (data) and for the row a dim-date parameter.  Result is that the count is down but date is not populated... 

What am I doing wrong? I can't figure it out... 

[Swapnilmandloi · Accepted solution]

It sounds like there might be an issue with how the relationship is being set up or how the calculated column is being interpreted. Let's go through a detailed step-by-step approach to troubleshoot and resolve this issue.

Step-by-Step Guide to Troubleshoot the One-to-Many Relationship Issue

1. Verify Data Types:

   • Ensure that the data types for the commencement_date and replacement_date columns are both set to Date in Power BI.
   • Also, make sure the dim-date parameter column in your dimension table is of Date type.

2. Check the Calculated Column:

   • Confirm that the calculated column replacement_date is correctly calculated.
   • Ensure the DAX formula is correct:
     DAX

     replacement_date = 'sql_replacements'[commencement_date] + (15*365.25)
   • Alternatively, use the DATEADD function to handle date arithmetic:
     DAX

     replacement_date = DATEADD('sql_replacements'[commencement_date], 15, YEAR)

3. Inspect the Relationship:

   • Go to the Model view in Power BI.
   • Ensure there is an active one-to-many relationship between the replacement_date column in the sql_replacements table and the dim-date column in your dimension table.
   • The relationship should be set to active, with the correct cardinality (one-to-many).

4. Verify Date Formats:

   • Double-check that all date columns are formatted as Date and not Text.
   • You can use the DATE function to ensure proper formatting:
     DAX

     replacement_date = DATE(YEAR('sql_replacements'[commencement_date]) + 15, MONTH('sql_replacements'[commencement_date]), DAY('sql_replacements'[commencement_date]))

5. Check for Matching Dates:

   • Ensure that the replacement_date values in the sql_replacements table match corresponding dates in the dim-date table.
   • If there are no matching dates, the relationship will not populate the rows correctly.

6. Test the Relationship:

   • Create a simple table visual with replacement_date from the sql_replacements table and a column from the dim-date table to ensure they are correctly related.
   • If the relationship is working, you should see corresponding values from both tables.

7. Create a Measure for Count:

   • Instead of directly using the count in the visual, create a measure to count IDs:
     DAX

     CountID = COUNT('sql_replacements'[ID])
   • Use this measure in your visual to ensure the counts are correct.

Example Steps

1. Create Calculated Column:

   DAX

   replacement_date = DATEADD('sql_replacements'[commencement_date], 15, YEAR)

2. Verify Relationship:

   • Ensure the relationship is active and set correctly.

3. Create Measure:

   DAX

   CountID = COUNT('sql_replacements'[ID])

4. Create a Visual:

   • Use a table visual and add dim-date column as rows.
   • Add the CountID measure to the table.

Troubleshooting Tips

• Check for Data Issues: Ensure there are no NULL or blank values in the date columns.
• Inspect Filters: Make sure there are no conflicting filters applied in the report that might affect the relationship.
• Test in a New Report: Sometimes starting fresh can help isolate the issue. Create a new Power BI file and import only the necessary tables to test the relationship.

By following these steps, you should be able to diagnose and resolve the issue with the one-to-many relationship not working correctly in your Power BI report.

[PowerRookie]

Perfect, thanks! It was not about the data-type but the exact value of the calculated column. 

the function EDATE did the trick. 

//////

replacement_date =

EDATE(sql_replacements[commencement_date],(15*12))
//////

tnx!