No active relationship measure - working in test but not in my main file

[GeorgeColl]

Hi,

I'm having an issue in my report with measures where I am (intentionally) not activating a relationship between my Policies table and the date table, this is because I am looking for the number of policies that are active on the last date of each year. 

I believe these measures worked fine for me previously, however I have recently noticed that any measures where I am not activating a relationship between the fact table and the date table are now returning no results at all. 

I have tested this in a new file and it works as expected, I have provided details below of the measure and the test I have used which reflects the relevant parts of my report (albeit there are more tables and relationships in the actual report).

My question is what would possibly cause a measure like this to stop working? Any direction or thoughts would be incredibly helpful and I'm happy to provide more info if required but I cannot provide my original report as it is confidential. I have pasted an image below of the result I get on the test vs what I get on my report.

Relationships

Date[Date] 1-Many Polices[Inception Date] (Inactive)

Date[Date] 1-Many Polices[Expiry Date] (Inactive)

Policies[SchemeID] Many-1 Scheme[SchemeID] (Active)

Date Table 

1/1/21 - 31/12/27

Scheme Table

SchemeID	Scheme
1	A
2	B

Policies Table

PolicyID	Inception Date	Expiry Date	SchemeID
1	01 April 2024	31 March 2025	1
2	01 May 2024	30 April 2025	1
3	06 April 2024	22 May 2024	1
4	08 August 2022	31 March 2025	1
5	09 September 2025	02 December 2026	2
6	01 January 2021	01 January 2027	2

Measure

Policies in Force (As at end of period) = 

VAR dateMax = MAX('Date'[Date]) //End of period

RETURN

CALCULATE(
            COUNTROWS('Policies'),
            FILTER('Policies',
                'Policies'[Expiry Date] >= dateMax &&
                'Policies'[Inception Date] <= dateMax))

Result in Test File

Test File - Result

Result in Actual

Result in Actual

[v-kpoloju-msft]

Hi @GeorgeColl,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @sjoerdvn, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.

Dax Measure for Policies in Force (As at end of year):

Policies in Force (As at end of year) = 
VAR SelectedYear = SELECTEDVALUE('DateTable'[Year])
VAR _EndOfYear = DATE(SelectedYear, 12, 31)

RETURN
CALCULATE(
    COUNTROWS('Policies'),
    FILTER(
        ALL('Policies'),
        'Policies'[Inception Date] <= _EndOfYear &&
        'Policies'[Expiry Date] >= _EndOfYear
    )
)

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[GeorgeColl]

Hi @v-kpoloju-msft thanks for your reply and sorry for the delay in mine, I had some unexpected leave

I'm afraid I'm still having the same issue, the amended formula gives a result in my test but I'm getting the same blank result in my main file.

I've tried to simplify further and just look at the Max date I was using earlier and I am getting a strange result when I'm not activating any relationship between AllPolicies and my Date table in my main file. I've put details below, and the problem I cannot solve is why the no-relationship formula is giving the incorrect result in my actual file. Any guidance on the possible reasons why would be most helpful.

Formulas used here:

Date inception format test = 
CALCULATE(
    MAX(AllPolicies[InceptionDate]), 
    USERELATIONSHIP(AllPolicies[InceptionDate], 'Date'[Date]))

Date inception format test no relationship = 
CALCULATE(
    MAX(AllPolicies[InceptionDate]))

Test File Result (as expected):

Actual File Result:

[johnt75]

It looks like there is a chain of active relationships from Date to AllPolicies, either through the Scheme table or more likely through some other table(s). I guess that when you created your test file you only created the tables needed for this calculation, i.e. Date, Scheme and AllPolicies, and that is why it works.

Check every table that Date has an active relationship with, and then check each of those tables for relationships and so on. 

[GeorgeColl]

@johnt75 Hi John,

You are right there are quite a number of additional tables. I'm using a star schema structure with my fact tables linked to the date table and to various common dimension tables.

I think you must be right with a model problem, but what would I be looking for that would break it? There's no links between my fact tables and my common dimension tables are linked to fact tables that are then linked to the date table.

Not sure if this will be visible enough to be helpful, and to be honest appreciate it's be almost impossible to follow some relationships. But I've put the structure below incase there's anything obvious

[johnt75 · Accepted solution]

The kind of thing you're looking for appears to be the CAClaims table, if I'm reading it correctly. Date filters that table, and then CA Claims seems to filter CA Action Required and another table I can't make out. I don't think that this is causing the problem but its the sort of thing that could.

Have a look in the Manage Relationships dialog for any tables which are on different ends of different relationships. Ideally all relationships for a table would either flow out of or into it, you're looking for tables where some flow in and some flow out.

[GeorgeColl]

Hey John,

I've identified the problem relationship so I've marked your above as the official answer (thank you!).

I've identified the two relationships highlighted below, if I make either of these inactive then it appears to fix the issue I'm having. My only confusion is that I believed this table was just acting as a dimension table so I'm not sure why it's working the way it is?

(I believe I understand that it's filtering because of the active relationships: Date - allproposals - Proposal Line Notes - AllPolicies. But I thought this was how all my other dimension tables work so i'm not fully sure why this one causes an issue?)

The ID used to link to each fact table is the same. If you could shed any light additional light that would again be much appreciated!

[johnt75]

To fix it it might be enough to change the relationship between All Proposals and Proposal Line Notes to a single direction, rather than bi-directional.

As for why its having the effect it is, the best I can do it point you to a couple of SQLBI articles, they explain it better than I ever could.

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/  and https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/ 

[GeorgeColl]

Really appreciate your help and time with this thanks a ton, the single direction relationship did the trick so that's a massive help.

I will definitely have a look through those articles as I've definitely got a gap in my understanding here!

[v-kpoloju-msft]

Hi @GeorgeColl,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @GeorgeColl,

I wanted to check if you had the opportunity to review the information provided. 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 and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @GeorgeColl,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[sjoerdvn]

you can always deactivate a relationship for the scope of a measure; within your calculate you can add an argument
CROSSFILTER('Date'[Date],Policies[Inception Date], NONE)

[GeorgeColl]

Hey sorry for my delayed response

The relationship is already inactive in the model (both my test file and my actual file) unfortunately so I don't believe there's an issue there

[sjoerdvn]

Have you checked your Date table? Does it have all required values? Maybe add a measure with value MAX('Date'[Date]) to the visual to test.

[GeorgeColl]

Unfortunately I have tested and confirmed that the max date is coming out correctly, if it is worth noting I do get results if I just activate one of the relationships. However by doing that the results become incorrect.

(Incorrect because with the inception date relationship being active for example, a policy may incept in the year 2024 but still be active at the end of 2025)