How can I make my measure be affected by other dimension filters in my model?

[GeorgeColl]

Hi,

I have a measure which calculated the number of renewing policies in a month by looking at the policies expiring within the start and end of that month.

The DAX measure I am using is:

Count of Renewing Policies = CALCULATE (     COUNT ( AllPolicies[PolicyID] ) , FILTER( ALL( AllPolicies ), ( [ExpiryDate] + 1 ) >= MIN ( 'Date' [Date] ) && ( [ExpiryDate] + 1 ) <= MAX ( 'Date' [Date] )  ),  AllPolicies[CaseStatusID] IN {62, 69, 87, 96} )

I'm now looking at some other dimensions and looking to filter this measure by one of the other dimensions, however I believe because I use ALL it is ignoring the other filters and I have been unable to figure out how to do this.

I've provided some details below to help with context but I can quickly reply if anything further required.

Example data:

Policy name	PolicyID	InceptionDate	ExpiryDate	CaseStatusID	BrokerID
Pol1	1	1/4/23	31/3/24	62	10
pol2	2	2/3/24	1/3/25	69	15
pol3	3	18/3/23	17/3/24	62	15
pol4	4	12/3/23	11/3/24	62	15
pol5	5	7/3/23	6/3/24	69	8

BrokerID	Broker Name
8	A
10	B
15	C

Target Table:

(Filtered by March only)

Year	2024	2025
Broker Name	Count of Rewnewing Policies	Count of Renewing Policies
A	1	0
B	0	0
C	2	1

Relationship to dimension

[Ashish_Mathur]

Hi,

Follow the technique shown in this article to create one row for each month for each policy.  Thereafter the prolem should be very simple to solve.

[v-nuoc-msft]

Hi @GeorgeColl 

I modified your code:

Count of Renewing Policies = 
CALCULATE(
    COUNT(AllPolicies[PolicyID]),
    FILTER(
        AllPolicies,
        (AllPolicies[ExpiryDate] + 1) >= MIN('Date'[Date]) &&
        (AllPolicies[ExpiryDate] + 1) <= MAX('Date'[Date]) &&
        AllPolicies[CaseStatusID] IN {62, 69, 87, 96}
    )
)

Based on the data you provided, the final results are as follows:

Regards,

Nono Chen

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

[GeorgeColl]

Hi Nono,

I've been really daft in my previous reply and to avoid confusion I've separated this response.

I am still having issues with my table now showing the count of renewing policies, I believe it's because the date filters of the column are applying and then the measure is being calculated (as shown below, where only 1 policy is appearing, which is the 05/07/23 expiry), but my table is filtering all policies by inception date (see below).

I am looking to show information on policies with an inception date of XX/07/23, but the column for renewing policies would show policies that are typically a year prior to this date.

I have tried the following forumula but it doesn't work, is there a way to do this or have I implemented the change wrong? My addition is bolded below:

Count of Renewing Policies = CALCULATE(

    COUNT(AllPolicies[PolicyID]),

    REMOVEFILTERS('Date'[Date]),

    FILTER(

        AllPolicies,

        (AllPolicies[ExpiryDate] + 1) >= MIN('Date'[Date]) &&

        (AllPolicies[ExpiryDate] + 1) <= MAX('Date'[Date]) &&

        AllPolicies[CaseStatusID] IN {62, 69, 87, 96}

    )

)

[v-nuoc-msft]

Hi @GeorgeColl 

Try this:

Count of Renewing Policies = 
CALCULATE(
    COUNT(AllPolicies[PolicyID]),
    REMOVEFILTERS('Date'[Date]),
    FILTER(
        AllPolicies,
        AllPolicies[ExpiryDate] >= DATE(YEAR(MIN('Date'[Date])) - 1, MONTH(MIN('Date'[Date])), DAY(MIN('Date'[Date]))) &&
        AllPolicies[ExpiryDate] < DATE(YEAR(MIN('Date'[Date])), MONTH(MIN('Date'[Date])), DAY(MIN('Date'[Date]))) &&
        AllPolicies[CaseStatusID] IN {62, 69, 87, 96}
    )
)

Regards,

Nono Chen

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

[GeorgeColl]

Hi Nono,

I had a look at the example you posted before to see if there was some difference, I originally missed that you were using the expiry date for the filter. I have changed the file into the format I am using in the report which should hopefully help explain (I apologise I missed this originally).

To explain, the table is being filtered to look at the Month & Year, where most of the data in the policy is being filtered by inception date. The renewing policies however need to not be filtered and only count ones based on the expiry date.

It says I can't attach a .pbix file so I've attached screenshots of the changes which hopefully make it clearer

[GeorgeColl · Accepted solution]

I did it! Thank you @v-nuoc-msft  for having a look into this I really appreciate it.

For anyone else working with multiple dates in the same fact table I strongly recommend this video by Enterprise DNA , my solution was to make both of my date relationships inactive and just activate them in the measures when required. 

For completeness the final formula I used was:

Count of Renewing Policies = 
CALCULATE(
    COUNT(AllPolicies[PolicyID]),
    ALL('Date'[Date]),
    USERELATIONSHIP(AllPolicies[ExpiryDate], 'Date'[Date]),
    FILTER(
        AllPolicies,
        AllPolicies[ExpiryDate] >= DATE(YEAR(MIN('Date'[Date])) - 1, MONTH(MIN('Date'[Date])), DAY(MIN('Date'[Date]))) &&
        AllPolicies[ExpiryDate] <= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date])), DAY(MAX('Date'[Date]))) &&
        AllPolicies[CaseStatusID] IN {62, 69, 87, 96}
    )
)

[v-nuoc-msft]

Hi @GeorgeColl 

Thanks for your sharing!

Regards,

Nono Chen

[GeorgeColl]

Hi Nono,

Thank you so much for having a look. EDIT: Not removing this as it may be helpful and don't want to cause confusion by deleting messages, but I have re-replied as the below is not correct.

I have made the change exactly as you have suggested, and I believe it is working to filter down the table to the correct policies. However, for some reason the count aspect is not working for me? I've attached an image to show what I mean, I have clicked the highlighted cell in the table and you can see that it has filtered correctly below to show 136 policies which I think is correct, but the count in the table is only showing 0 despite there being policy IDs to count as shown below. Is there any reason this might be? The main table should show 136 for the cell I've clicked, which is the count. I've tried using other count functions or counting other fields but can't seem to find a way to make it work.

@v-nuoc-msft I've been really trying this morning to work out why this isn't working with the count aspect of the measure, is there any reason it might not be working in my case?