Dax formula works on test but I only get blanks on main data

[GeorgeColl]

Hi,

I have a DAX formula which I want to calculate the number of policies in force during a period (in the below example per year), the formula counts any policy that has been active at any point during that year.

In my test model the DAX works completely fine and gives the correct results shown below, however when I try to use the measure in my real report I just receive blank values. The test model is a subsection of my data but all these are the same as in the real model, and I believe to be the only relevant parts used in the measure.

If I use a relationship in the real model then I do get values instead of blanks, but these are incorrect as i need to not filter the dates.

Is there any direction for me to look at or something that would cause the values to be blank in the real model?

Picutres below are of the test model:

[v-rzhou-msft]

Hi @GeorgeColl ,

I have a test based on your test data model. It works well on myside. I think your issue should be caused by the data model in your real model. I suggest you to try ALLSELECTED() or ALL() FUNCTION.

Policies in Force =
VAR dateMin =
    MIN ( 'Date'[Date] )
VAR dateMax =
    MAX ( 'Date'[Date] ) // Min and maX dates in period
RETURN
    CALCULATE (
        COUNTROWS ( AllPolicies ),
        FILTER (
            ALLSELECTED ( AllPolicies ),
            AllPolicies[Expiry Date] >= dateMin
                && AllPolicies[Inception Date] <= dateMax
        )
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GeorgeColl · Accepted solution]

Hi Rico,

Thanks for having a look into this for me and appreciate it was probably very difficult as it's hard to give too much from my primary model due to confidentiality. Unfortunately the above didn't solve my problem so I reverted to dismantling parts of my main model until the formula worked.

It turns out that I had a many-to-many relationship between two other tables in my model (this hadn't caused any other issues with any other measures, and I'm still not fully sure why the relationship would affect this measure because it isn't connected to the data being used in the measure - if anyone knows I'd be curious to know why!). I've put a picture below to help show this but apologies as I've had to remove the names, but I've highlighted what was an active many-to-many relationship and upon de-activating it, the formula now works.

Hopefully this can be helpful for someone else in future or atleast a good starting point if you have a measure that works otherwise. 

[dharmendars007]

Hello @GeorgeColl , 

Ensure the relationship between AllPolicies and Date tables is active in the real model. Inactive relationships may cause your measure to return blank and also Confirm that the Date table contains continuous dates without gaps.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[GeorgeColl]

Hi @dharmendars007, 

Thank you for taking a look and your suggestion.

I checked that there were no gaps in the date table, unfortunately in my reports I use multiple relationships between the two tables so I find it easier to have them all inactive and activate them on a case-by-case basis. In this case there's also not an appropriate relationship between the tables as policy length may cause a policy to be active over multiple years, where each date column only holds a single year and if the relationship was active it would filter out some policies.

I've found the solution and have marked it in my other response if you were interested. Thanks again for looking.