Change Date range in a Calendar Table

[hmeltonPSL]

I would like to extend the date range in my calendar table formula below.

Calendar = CALENDAR(DATE(2016,1,1),DATE(2021,12,31))

I changed the end date year to 2025 but when I save it I get the following error:

Any ideas?  This is in the MIcrosoft Executive Summary Report.  Thank you.

[WinterMist]

@hmeltonPSL 

Excellent!  So glad it worked for you.

Additionally, I have been looking for a way to NOT have to update the Calendar code over time.

Similar to other users, we don't want the limitations of CALENDARAUTO.

However, with CALENDAR, we don't want a hard-coded end date either.

That same post I shared also taught me how to do this, although I believe his end-date had a typo.

Start date MUST be 1/1, and end date MUST be 12/31 in DAX calendar for time intelligence to work properly.

I'm going to use something like this from now on, so I won't have to keep updating the end date year.

Hope this is helpful to you as it was to me.

Regards,

Nathan

[WinterMist · Accepted solution]

@hmeltonPSL 

Good news!  I think I found the answer.

Delete these 2 columns from the TenantMailboxUsage table:

1) "TotalIssueWarningQuota(KB/MB/GB/TB)"

2) "StorageUsed(KB/MB/GB/TB)"

Doing this causes the circular dependency error to disappear when changing the relationship from 1:1 to *:1 between the TenantUsageMailbox & Calendar tables.

Once the above steps were taken, I was finally able to edit the CALENDAR formula, and the USERELATIONSHIP error no longer appears!  (Changed end year from 2025 to 2022 as a test, and it worked.)

Hopefully this resolves both errors for you as it did for me!

Regards,

Nathan

P.S. Solution found here.

https://github.com/MicrosoftDocs/microsoft-365-docs/issues/4056

[hmeltonPSL]

I also noticed in the new template that I downloaded the date was extended to 2025 but I like knowing how things work so I change it again if I need to.  Thanks for all your hard work on this one!!!

[WinterMist]

@hmeltonPSL 

Haven't given up yet.

1) Unfortunately, I still do not have access to see any data in any table, other than Calendar.  Still waiting on permission to be granted for our Tenant ID.

2) Returning back to the original error messages:

   - USERELATIONSHIP error

   - Circular Dependency in table TenantMailboxUsage for fields:

      - MailboxStorageUsed

      - TotalIssueWarningQuota(KB/MB/GB/TB)

For the USERELATIONSHIP question, I'm still very confused.  No idea on that one.

For the Circular Dependency error, it refers to 2 Calculated Columns in the same table.

My guess is that it has to do with the following links, but I'm not sure yet.  Still investigating.

Apparently, there can be hidden circular dependencies when having 2 calculated columns within the same table.

- https://community.powerbi.com/t5/Desktop/quot-A-circular-Dependency-was-detected-quot-error-while/m-...

- https://www.sqlbi.com/articles/understanding-circular-dependencies/

Regards,

Nathan

[WinterMist]

Correction:

- This relationship actually does exist, but it is an ACTIVE relationship, not INACTIVE.

- The entire purpose of USERELATIONSHIP is to temporarily replace an ACTIVE relationship with an INACTIVE ONE.

- This is something I've never seen before.

- Why would USERELATIONSHIP be used at all when the pairing is already active?

Does anyone understand why USERELATIONSHIP is used here when the relationship is already active?

Regards,

Nathan

[WinterMist]

OK, the original error message is as follows:

"USERELATIONSHIP function can only use the 2 columns references participating in relationship."

This means that for the relationship pairings which appear under "Manage Relationships", USERELATIONSHIP must use inactive pairs that go together.

VALID Inactive Relationship Pairings
1) RegionActivity[UserID] = UserActivity[UserID]
2) UserActivity[TimeFrame] = Calendar[Date]
3) UserState[TimeFrame] = Calendar[Date]

Next I went through every measure in every table to find all measures which use USERELATIONSHIP.

USERELATIONSHIP does NOT exist in these tables
- Calendar
- TenantMailboxUsage
- TenantOffice365GroupsUsage
- TenantOfficeActivation
- TenantOfficeLicenses
- TenantOneDrive-Activity
- TenantOneDrive-Usage
- TenantProductUsage
- TenantSharePoint-Activity
- TenantSharePoint-Usage
- TenantSharePointUsage
- TopN
- UserState

USERELATIONSHIP DOES exist in these tables & underlying measures

- TenantProductActivity
   - (%)ChangeinActiveUserCount
   - (%)ChangeinActivityCount
   - (%)ChangeinAvg#ofActivities
   - PrevMonth(%)ChangeinAvg#ofActivities

       USERELATIONSHIP('TenantProductActivity'[TimeFrame],'Calendar'[Date])

PROBLEM: USERELATIONSHIP is calling a pairing that does NOT exist under Manage Relationships.

   - It does not exist in the list of the 3 VALID Inactive Relationship Pairings listed above.

   - As a result, every measure that does this (shown above) will cause this error.

   - Either 1 of 2 things needs to happen:

      - Change the model to add this relationship (OR)

      - Delete or comment out all measures which use this incorrect relationship pairing.

- UserActivity
- Multiple Measures starting with "EXO..."
       USERELATIONSHIP('UserActivity'[TimeFrame],'Calendar'[Date])

OK: This scenario is fine because the relationship pairing exists under Manage Relationships.

Conclusion: I would recommend you completely comment out all the measures shown above where no valid relationship exists.

Try that & let me know if the error goes away.

Regards,

Nathan 

[WinterMist]

@hmeltonPSL 

No need to apologize.  Thanks for all the links & information.

Still looking into things.  So far I've worked past the following Load errors:

1) A supported MIME type could not be found that matches the content of the response.

- FIX: Switched from Anonymous to Organizational Account

2) Tenant ID is not in the correct format

- FIX: Tracked down the correct Tenant ID

3) TenantOfficeLicenses: Access to the resource is forbidden

- FIX: Still in the process of requesting access to our Tenant ID.  Not there yet.

So unfortunately, I cannot yet load any data into the report, and have limited access to seeing certain things.

Under Modeling \ Manage Relationships, I can see that there are 3 inactive relationships.

I'm looking specifically for INACTIVE relationships because USERELATIONSHIP is designed to override an active relationship and replace it with an inactive one only for the duration of the measure where it is used.

Inactive Relationships
1) RegionActivity[UserID] = UserActivity[UserID]
2) UserActivity[TimeFrame] = Calendar[Date]
3) UserState[TimeFrame] = Calendar[Date]

Yeah, you're right.  The measures from the original error message don't use USERELATIONSHIP.  So I'm not sure why they appear in your error message (unless it's for a different reason).

As far as finding measures which use USERELATIONSHIP, I haven't learned a better way of doing this, than opening each measure one by one.  More research to do here as well.

Finally, I know you said you didn't write this template.

However, I just wanted to make sure the Calendar table was there to begin with, and you didn't make any changes to its relationships with any of the other tables.

Is that right?

I ask because:

1) Bi-directional relationships are to be avoided if possible, and there are at least 5 in the model.

2) The complexity of the relationships within this model is significant.

Currently looking for circular dependencies as well.

This might take some time.

If anyone else sees the answer before I do, please speak up.

Regards,

Nathan

P.S.  Here is a screenshot of the entire model, which helps to see the whole picture.

[WinterMist]

@hmeltonPSL 

We should be able to determine root cause by seeing screenshots of the following:

1) The formula(s) using USERELATIONSHIP

2) The relationships (& connecting fields) defined between the 2 tables being used by USERELATIONSHIP.

For example, can you provide screenshots like this?

Regards,

Nathan

[hmeltonPSL]

I am not sure where USERRELATIONSHIP is being used.  Is there an easy way to find it?  I did not write this...it is a microsoft template.

[WinterMist]

@hmeltonPSL 

Thanks for sending that.

From the error message, the circular dependency is detected across the following fields within the same table.

TABLE: 'TenantMailboxUsage'

Field 1: [MailboxStorageUsed]

Field 2: [TotalIssueWarningQuota(KB/MB/GB/TB)]

Field 3: [MailboxStorageUsed] (not sure why this is listed a 2nd time)

1) Are the above 2 fields measures or calculated columns?  Can you send me a screenshot of their defining formulas?  The error is specific to the USERELATIONSHIP function, so I am guessing one of these fields has the USERELATIONSHIP function in it.

2) I did find the following post which looks similar to your issue, and it has the resolution as follows:

- Remove the .Date from the Calendar[Date].[Date] (within the USERELATIONSHIP function).

https://community.powerbi.com/t5/Desktop/USERELATIONSHIP-function-can-only-use-the-two-columns-refer...

3) I searched for the Microsoft Executive Summary Template to download as a Power BI model, but all I could find is a Word doc.  As a last resort, can you send me the link you used to download so I can replicate what you are doing?

Regards,

Nathan

[hmeltonPSL]

Sorry for all of the replies.

About Microsoft 365 usage analytics - Microsoft 365 admin | Microsoft Docs

https://download.microsoft.com/download/7/8/2/782ba8a7-8d89-4958-a315-dab04c3b620c/Microsoft%20365%2...

[hmeltonPSL]

I will see if I can find the link again...I downloaded a while back.

TotalIssueWarningQuota(KB/MB/GB/TB) = IF([IssueWarningQuota_calc]/1024 <= 1, FIXED([IssueWarningQuota_calc],2,1)& " " & "Bytes",IF([IssueWarningQuota_calc]/1048576 <= 1 && [IssueWarningQuota_calc]/1024 > 1, FIXED([IssueWarningQuota_calc]/1024,2,1) &" " & "KB",IF([IssueWarningQuota_calc]/1073741824 <= 1 && [IssueWarningQuota_calc]/1048576 > 1,FIXED([IssueWarningQuota_calc]/1048576,2,1) & " " & "MB",IF([IssueWarningQuota_calc]/1073741824 > 1 && [IssueWarningQuota_calc]/1099511627776 <= 1 ,FIXED([IssueWarningQuota_calc]/1073741824,2,1) & " " & "GB",IF([IssueWarningQuota_calc]/1099511627776 >= 1,FIXED([IssueWarningQuota_calc]/1099511627776,2,1) & " " & "TB","0")))))

MailboxStorageUsed = IF([TotalStorageUsed]/1024 < 1, FIXED([TotalStorageUsed],0,1)& " " & "Bytes",IF([TotalStorageUsed]/1048576 < 1 && [TotalStorageUsed]/1024 > 1, FIXED([TotalStorageUsed]/1024,0,1) &" " & "KB",IF([TotalStorageUsed]/1073741824 < 1 && [TotalStorageUsed]/1048576 > 1,FIXED([TotalStorageUsed]/1048576,0,1) & " " & "MB",IF([TotalStorageUsed]/1073741824 > 1 && [TotalStorageUsed]/1099511627776 < 1 ,FIXED([TotalStorageUsed]/1073741824,0,1) & " " & "GB",IF([TotalStorageUsed]/1099511627776 > 1,FIXED([TotalStorageUsed]/1099511627776,0,1) & " " & "TB","0")))))

[WinterMist]

@hmeltonPSL -

Is it possible for you to share the PBIX?

If security reasons prevent you from doing so, can you at least provide a screenshot of the model?

Regards,

Nathan

[hmeltonPSL]

I even made all the relationships with the Calendar table inactive and I still get the same message.  I am using the MIcrosoft Executive Summary Template from Microsoft.