Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
@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'
@garythomannCoGC nice one 🙂 glad to be of help.
@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>
@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
@garythomannCoGC yes, you are right. It is part of EVALUATE
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
@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
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?