Forum Discussion

ep11's avatar
ep11
Frequent Visitor
4 years ago

Delete sorting in final Dax Query

I have a pbix project. Through the perfomance analyzer, I was able to get Dax Query that my project generates and discovered an interesting thing. In my project does not contain any sorts. For some reason they are in the Dax Query:

 

 

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Table_Name'[col_date])),
AND(
'Table_Name'[col_date] >= DATE(2021, 3, 1),
'Table_Name'[col_date] < DATE(2021, 7, 1)
)
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Table_Name'[col_vachar1],
'Table_Name'[col_vachar2]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
"Sum", CALCULATE(SUM('Table_Name'[NewQty]))
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Table_Name'[col_vachar1],
1,
'Table_Name'[col_vachar2],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
'Table_Name'[col_vachar1],
'Table_Name'[col_vachar2]

 

 

I tried removing the last four lines of the query and running it. The request completed 25% faster. Is there any way to make my pbix project not use this sort? Is it possible through Power BI Desktop to edit the final Dax query that the project generates?

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ep11 , This Dax for you visual?

     

    can you check visual has no sorting?

     

    I doubt you can force your own DAX.

  • ep11's avatar
    ep11
    Frequent Visitor
    4 years ago

    >>This Dax for you visual?
    Yes, example with renamed columns

     

    Visually I have disabled all sorting

     

  • ep11's avatar
    ep11
    Frequent Visitor
    4 years ago

    I made a new project for testing, it also has sorting.

    Created a simple table:

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDI0AEIg0xGIDQ2AhFKsTrSSEULSCCppBJc0RpV0AmJjuKQJQhKkzhmITeCSppiSphDJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type text}})
in
#"Changed Type"

     

    For visualization included only these columns:

     

    As a result, in the Dax query I see sorting

    // DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Table'[Column3], "IsGrandTotalRowTotal"),
      "SumColumn4", CALCULATE(SUM('Table'[Column4]))
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table'[Column3], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Table'[Column3]

     

    Is there a way to avoid using this sort? Or is it required for data grouping? Sorting takes a long time on large amounts of data