DAX query for Power BI Report Builder report

[garythomannCoGC]

Wanting feedback on a dax query I am working on. The dax query feeds into a PBI Report Builder report.
Still on the learning curve with dax querys and to me the structure of this particular query seems unusual. My background is with SQL.
I did not create the report and tasked with enhancements etc. (And your thinking 'thats what they always say' ha ha 🙂
With what little more I now know with dax I would not have written the query this way but maybe this is the right way for integration into PBI RB.

Basically it is of the form

EVALUATE
VAR <table a> = FILTER ( SELECTCOLUMNS (
VAR <table b> = FILTER ( SELECTCOLUMNS (
VAR result = NATURALINNERJOIN <table a> <table b>
RETURN result

This does work but from the syntax of both EVALUATE and VAR, the VAR's should be in their own DEFINE section I believe.
And how do we sort the result?

I could not find any similar examples online and hopefully I have explained well enough.

Thanks in advance

[garythomannCoGC]

@smpa01   Thanks once again for your patience and help.  Moving forwards again 🙂 

Quote for the day   '<God> only provides the colours ... we have to do the painting'

[smpa01]

@garythomannCoGC  nice one 🙂 glad to be of help. 

[smpa01]

@garythomannCoGC  ordering

EVALUATE
VAR <table a> = FILTER ( SELECTCOLUMNS (
VAR <table b> = FILTER ( SELECTCOLUMNS (
VAR result = NATURALINNERJOIN <table a> <table b>
RETURN result

order by <date or something else>

[garythomannCoGC]

@smpa01 Thanks for your reply.  So I gather that the  order by should work here and that I should just perservere to get it going.  So that is good.  Can I assume the   order by  is part of the  evaluate  statement?

Am I heading in the right direction with syntax then?

ORDER BY
    'result'.[column] ASC

ie the  var 'result' table references will work

[smpa01]

@garythomannCoGC  yes, you are right. It is part of EVALUATE

[garythomannCoGC]

Ok so you can only sort by the primary key of the  naturalinnerjoin  statment by your example?

Can you sort by other values in the resultant psuedo table '_join' ?

The third column of _left and _right tables  does not have a label.  How do you reference that in the sort?  number maybe like in sql?

RETURN

    _join

ORDER BY 

    '_join'.[Ordinal] DESC,

    '_join'.[Name]  ASC,

    '_join'.3  ASC

[smpa01 · Accepted solution]

@garythomannCoGC  yes you can

THE DAX DDL which you can experiment upon

EVALUATE
VAR _left =
    DATATABLE (
        "Name", STRING,
        "Ordinal", INTEGER,"Color",STRING,
        {
            { "Small", 1,"red" },
            { "Medium", 2,"green" },
            { "Large", 3,"blue" }
        }
    )
VAR _right =
    DATATABLE (
        "Name", STRING,
        "Ordinal", INTEGER,
        {
            { "Small", 1 },
            --{ "Medium", 2 },
            { "Large", 3 }
        }
    )
VAR _join =
    NATURALINNERJOIN ( _left, _right )
RETURN
    _join
ORDER BY [Color] DESC

[garythomannCoGC]

Also how do we sort the resultant table (result) ? As part of the 'result' declaration? As part of the RETURN statement?

I did try and add the ORDER BY clause for EVALUATE statement and reference the 'result' table.
Tried various syntax the last being
ORDER BY
    'result'.[column] ASC

Is there any way around the need for ORDER BY to reference all the columns of a table?