Hi all,
Is there a way to make a slice of a table using Distinct records only?
Case: Table with montly payments of students Need: Get the Distint number of students per course of the last two months
Thanks and Happy New Year!
Where do you want to show them… in the same table view where you are showing students?
@Aristotelis_T You can use a combination of the COUNT expression and the SELECT expression. In the SELECT expression, there is a parameter to allow you to specify whether you want to count only distinct elements. The syntax is as follows:
SELECT({List},{Filter Yes/No}, {Optional Distinct Yes/No})
The overall expression would like something like this:
COUNT(SELECT([StudentColumn], {filter condition}, yes))
You don’t provide a lot of information, so I gotta make a lot of assumptions…
Assuming a table, Payments, with columns [Student] and [Course] identifying the individual student and course, respectively, for which a payment has been received, and the column [When], containing a Date, that specifies when payment was received for the student for the class.
Assuming a table, Courses, describing each course.
Assuming Payments[Course] is a Ref to Courses.
Create a Virtual Column in Courses named Paid Students with the App formula of =COUNT(SELECT(Payments[Student], AND(([Course] = [_THISROW]), ([When] >= (TODAY() - 60))), TRUE))
The [Paid Students] column in Courses will then have what you want.
Breakdown of the App formula for the [Paid Students] column:
([Course] = [_THISROW]): selects only records for this course.
([When] >= (TODAY() - 60)): selects only records within the last 60 days (change 60 as desired).
AND(…): both selection criteria must be met.
Payments[Student]: returns the list of students matching the selection criteria.
SELECT(…, TRUE): returns only distinct results (distinct values of [Student]).
COUNT(…): counts the number of results returned by SELECT().
User | Count |
---|---|
44 | |
35 | |
24 | |
23 | |
15 |