Data Model Relationships

[LearnToFly]

I have inherited an older dashboard with a preexisting data model which has a start time field that I tried to relate to a new table I created...through another field called workday.  So the relationship exists there was no error when it was created.  However, it will not allow me to compare two-time fields.  When I tried to use related to verify the relationship...it says The column 'Call Detail Record[Start_Time - Time]' either doesn't exist or doesn't have a relationship to any table available in the current context.  I have checked data types...both are long-time formats, and I have checked correct naming conventions and precision.  I have little experience with data modeling and I feel like it's the relationship because there isn't a primary key and it has to relate to a foreign key but I'm not sure what the workaround for this is.

I've tried just an IF, RELATED, and RELATEDTABLE...here is my code:

Business Hours =
IF (
'Hours_Of_Operation'[Start_Of_Day1] = RELATEDTABLE('Call Detail Record')[Start_Time -Time] ||
'Hours_Of_Operation'[Start_Of_Day2] = RELATEDTABLE('Call Detail Record')[Start_Time - Time],
"COUNT",
"DON'T COUNT"
)

*Creating a composite key didn't seem to work either.

[Anonymous · Accepted solution]

Hi @LearnToFly ,

You can update the formula of calculated column [Business Hours] as below:

Business Hours = 
VAR _count =
    CALCULATE (
        COUNT ( 'Call Detail Record'[Start_Time -Time] ),
        FILTER (
            'Call Detail Record',
            'Call Detail Record'[Start_Time -Time]
                IN { 'Hours_Of_Operation'[Start_Of_Day1], 'Hours_Of_Operation'[Start_Of_Day2] }
        )
    )
RETURN
    IF ( _count > 0, "COUNT", "DON'T COUNT" )

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and the relationship info between the tables. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards