first 100 rows with DAX to retrieve Tabular Data

Question

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

score 3 · Answer 1 · answered Mar 15 '16 at 00:59

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.

score 2 · Answer 2 · answered Mar 15 '16 at 13:00

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!

first 100 rows with DAX to retrieve Tabular Data

2 Answers2