cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JWick1969
Helper III
Helper III

PowerBI Report- Deleting some code on DAX Query generated from Performance analyzer

Hi

I have a DAXquery generated from Performance analyzer. it is restricting the number of records to 501 and inned to removed this and also how could I removed the sub toital and grand total from this script. I tried to delete some code but i'm getting an error. Not able to identify the exact code that i should be removed. Thank you. 

 

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({2021}, 'DateTable'[Received])

  VAR __DS0FilterTable2 = 
    TREATAS({"November"}, 'CCST'[MonthName])

  VAR __DS0FilterTable3 = 
    TREATAS({"LEGACY"}, 'CCST'[Model])

  VAR __DS0FilterTable4 = 
    TREATAS({"DIY"}, 'CCST'[Type])

  VAR __DS0FilterTable5 = 
    TREATAS({"Normal"}, 'CCST'[Status])

  VAR __DS0FilterTable6 = 
    TREATAS({"OTHERS"}, 'CCST'[Category])

EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], "IsGrandTotalRowTotal",
        ROLLUPGROUP(
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo]
        ), "IsDM0Total"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      __DS0FilterTable6,
      "SumReceived", CALCULATE(SUM('CCST'[Qty])),
      "Good", 'CCST'[Good],
      "Good__", 'CCST'[Good %]
    ),
    [IsGrandTotalRowTotal],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
    1,
    [IsDM0Total],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
  [IsDM0Total],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month]

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

The basic approach is that you want to keep the SUMMARIZECOLUMNS call inside the TOPN. If you use a tool like DAX Studio https://daxstudio.org or some other text editor which can do bracket highlighting you can then easily see where the call to SUMMARIZECOLUMNS ends and then you would just remove the bits of code before and after that (the items in red boxes below

d_gosbell_1-1639088720723.png

 

View solution in original post

2 REPLIES 2
d_gosbell
Super User
Super User

The basic approach is that you want to keep the SUMMARIZECOLUMNS call inside the TOPN. If you use a tool like DAX Studio https://daxstudio.org or some other text editor which can do bracket highlighting you can then easily see where the call to SUMMARIZECOLUMNS ends and then you would just remove the bits of code before and after that (the items in red boxes below

d_gosbell_1-1639088720723.png

 

Thank you very much. I'm using notepad++ text editor.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors