I'm trying to build an aggregate formula to tell us how many Policies we have that are in Active Status (Insurance Policy Data). I need it to be an aggregate formula so I can use it in pivot tables in conjunction with other formulas, and build other formulas off of it. I know I can get this data simply using filters, but I can't use filters in the pivot table because it affects my other data coming from other data tables.

So I need to get a distinct count of a Number field we have in a data table that houses Policy Numbers. I only want it to count Policies with a Status that equals Active. Ive tried building a nested if formula with distinct count but there is something wrong with the syntax because I keep getting an error.

Here is my formula:

distinctcount("BoB Detail Report - Current Month"."Policy Number",(if("BoB Detail Report - Current Month"."Status"='Active'))