Hi all, Is there a way to make a slice of a ...

[Aristotelis_T]

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!

[Aleksi_Alkio]

Where do you want to show them… in the same table view where you are showing students?

[Harry2]

@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))

[Steven_Coile]

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().