Count measure that doesn't change when new fields are added

Nels921 — Wed, 28 Sep 2022 19:49:14 GMT

I'm working on a query to identify accounts that have not submitted another form so that we can contact them and either deactivate the account or have them submit a new form for approval. This is a sample of the data I'm working with. The AccountID comes from the Accounts table and the FormID and form information from the Form table.

AccountID	FormID	Form Submitted On	Form Status
C222333	F45645634	12-Apr-21	Processed
C222333	F79230488	10-Apr-22	Pending
C455345	F89494345	21-Jun-21	Processed
C455345	F73423060	15-Jun-22	Pending
C783434	F56943005	21-Oct-21	Processed
C543405	F34549545	01-Aug-21	Processed
C093943	F23435923	15-Mar-21	Processed
C093943	F80349559	18-Apr-22	Pending

My initial approach was to create a measure to count how many forms each account has submitted and filtering for those that have only submitted 1 form, I want to see when they submitted it and what the status is. I used this measure for the count:

Number of Forms submitted = CALCULATE(COUNT(Formtable[FormID]),

GROUPBY(Accounts, Accounts[AccountID]))

then I used the Filters to select Number of Forms submitted is 1.

But when I try to see the Status and submitted date of these applications this is what I get

AccountID	Number of Forms submitted	Form Submitted On	Form Status
C222333	1	12-Apr-21	Processed
C222333	1	10-Apr-22	Pending
C455345	1	21-Jun-21	Processed
C455345	1	15-Jun-22	Pending
C783434	1	21-Oct-21	Processed
C543405	1	01-Aug-21	Processed
C093943	1	15-Mar-21	Processed
C093943	1	18-Apr-22	Pending

Which is not correct because only accounts C783434 and C543405 have 1 form. So the desired state would be

AccountID	Number of Forms submitted	Form Submitted On	Form Status
C783434	1	21-Oct-21	Processed
C543405	1	01-Aug-21	Processed

I've found that when I remove the submitted on and Form Status fields, then the data looks like it should and the counts are accurate, but when I add either of these two fields it shows all the accounts with the forms submitted as 1. I'm sure there's something wrong with my measure, I'd really appreciate some guidance on getting the desired result.

Re: Count measure that doesn't change when new fields are added

johnt75 — Thu, 29 Sep 2022 09:19:19 GMT

The columns you add into the table visual from the Forms table are acting as filters, so only one form is visible at a time. Try

Number of Forms submitted = CALCULATE ( COUNT ( Formtable[FormID] ), ALLEXCEPT ( Accounts, Accounts[AccountID] ) )

If you know that there will be only 1 entry per form in the Forms table then COUNTROWS(Formtable) would be better than the current COUNT.

topic Re: Count measure that doesn't change when new fields are added in DAX Commands and Tips

Count measure that doesn't change when new fields are added

Re: Count measure that doesn't change when new fields are added