3
votes

HOw to retrieve FIRST (N) rows while retrieving a Table from Power Pivot with Dax? What came to my mind is only to add an index column with Power Query and then to use FILTER() to enroll the SUMMARIZE()

My code:

EVALUATE
FILTER(
SUMMARIZE(
RTO_EnrolmentsAllCourses,
    RTO_EnrolmentsAllCourses[CampusName],
    RTO_EnrolmentsAllCourses[CoENo],
    RTO_EnrolmentsAllCourses[Competency],
    RTO_EnrolmentsAllCourses[Course_Finish],
    RTO_EnrolmentsAllCourses[Course_start],
    RTO_EnrolmentsAllCourses[CourseAttempt],
    RTO_EnrolmentsAllCourses[CourseID],
    RTO_EnrolmentsAllCourses[CourseName],
    RTO_EnrolmentsAllCourses[Index]
),
    RTO_EnrolmentsAllCourses[Index]<50)

Thanks in advance

2

2 Answers

2
votes

Try this:

EVALUATE(
 SAMPLE(
  50,
  RTO_EnrolmentsAllCourses,
  RTO_EnrolmentsAllCourses[CampusName], 1,
  RTO_EnrolmentsAllCourses[CoENo], 1
 )
)

That returns the first 50 rows ordered by CampusName ascending (that's the value 1 right after CampusName) and CoENo ascending. You have to provide order by columns of you want a predictable 50 rows rather than a random 50 rows per the documentation.

1
votes

Thanks, GregGalloway! It is working just fine. Enrolling SAMPLE () into SUMMARIZE() I just retrieve what I needed.

EVALUATE
 SUMMARIZE(
 SAMPLE(
  10,
  RTO_EnrolmentsAllCourses,
  RTO_EnrolmentsAllCourses[CampusName], 1
 ),
 RTO_EnrolmentsAllCourses[AgentName],
 RTO_EnrolmentsAllCourses[CampusName]
 )

Thanks again!