Ordering on a non-base table - Report Builder

[GeorgeColl]

Hi,

Apologies that this may be very straight forward but I cannot find any documentation or resource online. I have a table that is a union of two other tables, both containing an "Inception Date" field. I want to order my results of this table by the inception date, is this possible?

This is what I have currently tried

VAR __DS2Core =
		UNION(__DS0Core, __DS1Core)

EVALUATE
	 __DS2Core

ORDER BY
	'__DS2Core'[Inception Date] Desc

I have also tried without '__DS2Core' and it runs but does not order how I expect it to.

[v-nmadadi-msft]

Hi @GeorgeColl  ,
Thanks for reaching out to the Microsoft fabric community forum.

You can use this DAX code

CombinedTable =

    UNION(

        SELECTCOLUMNS(__DS0Core,

            "Name", __DS0Core[Name],

            "Inception Date", __DS0Core[Inception Date]

        ),

        SELECTCOLUMNS(__DS1Core,

            "Name", __DS1Core[Name],

            "Inception Date", __DS1Core[Inception Date]

        )

    )

to create a combined table between the first two tables.

In the table visual you can sort by descending order by clicking on epsilon.

I have shared the pbix file for reference as well.


If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[SamsonTruong · Accepted solution]

Hi @GeorgeColl 

Can you try the following query to order your fields. As a note, this solution will require you to explicitly define which fields you need from the union in the SELECTCOLUMNS() section:

EVALUATE
VAR __DS2Core =
    UNION(__DS0Core, __DS1Core)
RETURN
    SELECTCOLUMNS(
        __DS2Core,
        "Inception Date", [Inception Date],
        -- add all columns needed from __DS2Core
    )

ORDER BY
    [Inception Date] DESC

 
Please let me know if this achieves your desired result.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson