Multiple USERELATIONSHIPS functions within an AND and within a CALCULATE

[D_PBI]

Hi,
I have two inactive relationships between the [__dimDate] and [agreement] tables, those being:
__dimDate[Date] <> agreement[TerminatedDate]
__dimDate[Date] <> agreement[ExpirationDate]

I'm attemtping to create a measure that counts unique agreementid records where the Termination Date is greater than a variable and the Expiration Date is greater than a variable, or Agreement Status isn't one of the listed values. See below for the attempted measure.

The above DAX is returning the error "USERELATIONSHIP function can only be used in the CALCULATE function."

Please can someone help me get the measure to use both USERELATIONSHIPS functions working?
Thanks.

[D_PBI]

@v-sgandrathi thanks for your efforts. They're appreciated. However, the 'Test_Licences' measure you've created isn't following the logic in my measure, the logic I need.
Your logic is creating a seperate table per date filter. This will return a greater number of records and there is less filtering applied in one go. My measure, my logic, applies all the date filtering in one go and so only the records that match that criteria will be returned. As more filters are applied, the number of records returned will be less.

The trouble I'm facing is being to use the USERELATIONSHIP functions multiple times within the same filter. In my DAX measure example above, you will see two parts to the measure, separated by an || (OR). The top half of the measure uses several USERELATIONSHIP functions - this is not working. In the bottom half of the measure, it uses only a single USERELATIONSHIP function but even still it's not working.
For some reason the USERELATIONSHIP isn't working at all and I don't understand why.

[v-sgandrathi]

Hi @D_PBI,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[v-sgandrathi]

Hi @D_PBI,

I wanted to check if you had the opportunity to follow up on our previous conversation. If yes can you please provide the sample data so that we can provide you with the accurate and correct solution. 

Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-sgandrathi]

Hi @D_PBI,

We strive to make sure every issue gets addressed completely. If the previous answer helped, that’s great, please mark it as Accepted and consider giving it a Kudos.

Still facing issues? Just reply here and we’ll be right with you.

Thanks for being a valued part of the community,

[v-sgandrathi]

Hi @D_PBI,

Hope everything’s working fine now! If you’ve found a fix, feel free to share it and mark the answer that helped you as a solution. It really helps others in the community.

If we don’t hear from you soon, we’ll close this thread for now. But if you ever need help again, just start a new thread, we’re always here to support you.

Thank you.

[v-sgandrathi]

Hi @D_PBI,

Thank you for providing detailed information on your issue. I’ve reviewed your scenario and successfully replicated it using sample data on my end.

I’m sharing the working .pbix file below so you can explore the setup and DAX logic more clearly. Please feel free to open it and review how the relationships and measures are configured:

I hope this helps resolve your issue. If you have any questions or need further clarification, I’m happy to help.

If you found this post useful, kindly give it a ‘Kudos’ and mark it as a solution so others facing similar challenges can find it more easily.

Thanks for being part of the Microsoft Community!

[D_PBI]

@wardy912 thank you for your suggestion. Below is my attempt to use it. Unfortunately, it's not working.
I just want to be clear on this. 
The _dimDate table has the following fields:
_dimDate[Date]
_dimDate[Financial Year]
_dimDate[Financial Quarter]
_dimDate[Month]

All the other fields of interest are present within the agreement table (for example, agreement[Agreement Status], etc..).

The relationships between the _dimDate and the ageement tables are as follows:
_dimDate[Date] <> agreement[Execution Date] - this is the active relationship.
_dimDate[Date] <> agreement[Expiration Date] - inactive relationship.
_dimDate[Date] <> agreement[Termination Date] - inactive relationship.

The final output will be a Matrix visual with the dimDate hierarchy (i.e. Year, Quarter, Month) for the rows and the agreement[Team Name] for the columns. The number present will be from the measure (shown below).

Below is my attempt. It doesn't work. The DAX is accepted but the number(s) returned is way off.
I created a new page which had a table containing the agreementid, termination date, expiration date, execution date, autm categorization, and agreement status. The same page also has filters for those same fields. I used this page to manually filter a simple DISTINCT COUNT of the agreement[agreementid] field. The dates filtered were from the agreement table and not the _dimDate table (i.e. a filter visual would use the agreement[Termination] date on this new page). This page provided me with the precise numbers I should be expecting.

When filtering little by little between the new page and the below measure, I can see it is the use of the dates filtering where the measure fails. In the DAX screenshot below, I have circled in red the area of failure - each area that uses this CALCULATE/COUNTROWS/USERELATIONSHIP functions is failing.

@speedramps - thanks for your suggestion. I tried your suggestion too. It's the same as with the other suggestion. It's failing with the dates logic. Removing the dates and filtering simply on agreement[autm categorization] returns the matching number to when filtering the agreement[agreementid] using visual filters. As soon as I bring in just one (any one) of the date filters in the DAX it doesn't work.

Below is the new page I created to filter the agreement[agreementid] through visual filters. You see in the screenshot below that I use 'Edit interactions' to filters the measure by single filters at a time. In the below screenshot, you'll see only the Termination Date filter is used and it returns 47. The measure, when only filtering on the Termination Date, returns 24086.

This is what's happening through both attempts.
Finally, I use the CONTAINSSTRING function as the string is checking if it contains the value/text. It isn't an exact match, as the IN {} would check for, that I'm looking for.

Thanks again, @wardy912 , @speedramps  and @mark_endicott 
If you can help me with this, it will be greatly appreciated. Thanks.

[v-sgandrathi]

Hi @D_PBI,

Has your issue been resolved?
If the response from the community member answered your query, please consider confirming it. Marking a reply as a solution helps others with similar questions and keeps the community helpful and organized.
Thak you @mark_endicott, @speedramps, @wardy912 for your responses.
If the response was helpful, a Kudos is always appreciated!

[mark_endicott]

@D_PBI - You need to take the USERELATIONSHIP filters out of the FILTER statement, and add them as additional filters. 

Also, I would not recomment using FILTER( 'Table', 'column' ) as you have, this is a classic anti pattern and will result in a slower calculation, you should filter a column rather than a table. Here is a good article for this: https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/

One final thing, I would recommend simplfying your final condition to use NOT IN {"Value1", "Value2" } etc.

I hope this helps, if you need more please provide the DAX code outside of a screenshot and some sample data. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[speedramps]

Perhaps you do not need the use the relationships

Simply create temporary subset with just the rows you need.

Then count the agreements in that temporary subset 

Like this

Answer =

var mydare = TODAY()   //  you can repace this with your variable

var mysubset =
FILTER( yourtable,
(youttable[terminationdate] > mydate &&
youttable[expiryndate] > mydate ) ||
youttable[expiryndate] not in {"Cancelled", "Died", "Rejected"}
)

RETURN
CALCULATE(
DISTINCOUNT(yourdate[addrmentID),
mysubset
)

You can learn about role based date relationships here (click to download form Onedrive) 

Role based date relationships.pbix

Look at all the pages, the relationship structure and dax measure to see how it works.

I have added comments to help you learn

Please click thumbs up because I have tried a lot to help.

Then click accept solution if it works.

Many thanks

[wardy912]

You're encountering the error because USERELATIONSHIP must be used inside a CALCULATE function.

You need to use CALCULATE with USERELATIONSHIP for each inactive relationship separately, and then combine the logic.

Try this:

Measure =
VAR _cutoffDate = DATE(2024, 01, 01) -- Replace with your variable
RETURN
CALCULATE(
DISTINCTCOUNT(agreement[agreementid]),
FILTER(
agreement,
(
CALCULATE(
COUNTROWS(agreement),
USERELATIONSHIP(__dimDate[Date], agreement[TerminatedDate])
) > 0 &&
agreement[TerminatedDate] > _cutoffDate
)
||
(
CALCULATE(
COUNTROWS(agreement),
USERELATIONSHIP(__dimDate[Date], agreement[ExpirationDate])
) > 0 &&
agreement[ExpirationDate] > _cutoffDate
)
||
NOT agreement[AgreementStatus] IN {"Terminated", "Expired"} -- Replace with your list
)
)