topic Re: Count distinct, but only once per dimension in App Development

Count distinct, but only once per dimension

Lauri — Wed, 02 Mar 2022 18:54:01 GMT

I have a table with a dimension (Clinic) and a measure (count of distinct patients who had a visit). How can I make the measure count patients only once in the table, for their most recent visit?

For example, the Clinic dimension has values 'Clinic A' and 'Clinic B.' If patient Mary went to both clinics, she should be counted in the 'Clinic A' row because she went there most recently.

In the data model, the fields are all in one table: Clinic, PatientID, VisitDate.

Should I use set analysis with Aggr?

Re: Count distinct, but only once per dimension

Vegar — Wed, 02 Mar 2022 20:22:42 GMT

You could try this expression:

=-SUM(Clinic=aggr(nodistinct FirstSortedValue(Clinic, -VisitDate),PatientID))

Re: Count distinct, but only once per dimension

Lauri — Fri, 04 Mar 2022 04:11:57 GMT

Thank you, @Vegar . I don't completely understand. Do you mean that can be my measure expression? Or is that set analysis inside of count(distinct PatientID)? It looks like set analysis but without {} and <> brackets.