Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Swalker
Frequent Visitor

Sortorder in Report Builder

Hi,

 

In Power Bi I have a FactTable of 227 project records with Start Dates starting from 1 Jan 2017 till 26 jan 2023. In PQ i added an Index Column starting from latest Start Date  26 jan 23 (Index 1) till 1 Jan 2017 (Index  227)   .

I am loading in Reporting Builder 5 fields from the PowerBI dataset: Year (=YEAR([Date])) ClientType (text) Location(text) Project (text) Sector(text) using the Query

 

EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector])

 

In Report Builder a Tablix is used and all 5 Fields are dropped in the  Value  box

 

The issue: The Sorting Order is not correct. 

There are 5 pages and 46 lines per page. The first 3 pages is in ascending order till 1 jan 2017. 

But then the Project Years start with 2021 , then 2020,  then 2019, then 2022, then 2022 , then 2018...later 2023 the year are all over the place.

How can I force a sorting order (descending from Start dates 2023 -> 2017) either at the source data or in the Query ?

 

 

Many thanks !

 

1 ACCEPTED SOLUTION
Swalker
Frequent Visitor

I have solved this issue as follows:

 

EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector]) ORDER BY 'ReferenceList'[Index] ASC

 

 

* include column [Index] in EVALUATE statement 

* use ORDER BY [Index] ASC 

View solution in original post

1 REPLY 1
Swalker
Frequent Visitor

I have solved this issue as follows:

 

EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector]) ORDER BY 'ReferenceList'[Index] ASC

 

 

* include column [Index] in EVALUATE statement 

* use ORDER BY [Index] ASC 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Users online (999)