cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
silvywilson
New Member

DAX query using same measure creates different SQL one of which throws error

I created a measure that is being used as a drill-through category filter.  Two different visuals use this measure as a drill through category filter.  One of the visuals works without any problem but the other throws an error.  The error is:

"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information."

 

If I uncheck the filter there is no error or if I pick a category with less number of items to filter against, it works.  The sql generated is different in the two cases.  For example. in the case of the one that works, there is the OR in the sql whereas in the case of the one that does not work, there is no OR.  I believe the problem is associated with too many items in the IN statement.  So for the case where it works, the IN ss broken up with the OR.  My question is why are the two generated SQLs different and is there a way to rectify this?

 

OR  ([t14].[dev_id] IN (100523476,100527408,100532926,100537411,))

Works Well:

DEFINE
  VAR __DS0FilterTable =
    FILTER(
      KEEPFILTERS(VALUES('TBSdevice_mst'[ModelGroup])),
      AND('TBSdevice_mst'[ModelGroup] IN {"5AC"}, 'TBSdevice_mst'[ModelGroup] IN {"5AC"})
    )
  VAR __DS0FilterTable2 =
    FILTER(
      KEEPFILTERS(VALUES('AllModelPredictionView'[RoundedProbScore])),
      AND(
        'AllModelPredictionView'[RoundedProbScore] >= 0.6,
        'AllModelPredictionView'[RoundedProbScore] <= 0.99
      )
    )
  VAR __DS0FilterTable3 =
    FILTER(
      KEEPFILTERS(VALUES('CalendarDates'[date])),
      AND(
        'CalendarDates'[date] >= DATE(2020, 3, 26),
        'CalendarDates'[date] < DATE(2020, 4, 23)
      )
    )
  VAR __DS0FilterTable4 =
    FILTER(
      KEEPFILTERS(VALUES('TBSdevice_mst'[IsValidModel])),
      'TBSdevice_mst'[IsValidModel] = 1
    )
EVALUATE
  TOPN(
    1001,
    SUMMARIZECOLUMNS(
      'TBSdevice_mst'[ModelGroup],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      "Counterror_code", CALCULATE(COUNTA('error_log'[error_code]))
    ),
    [Counterror_code],
    0,
    'TBSdevice_mst'[ModelGroup],
    1
  )
ORDER BY
  [Counterror_code] DESC, 'TBSdevice_mst'[ModelGroup]
 
 
 
 
  ----------------------------------------------------------------
Does not work well:
 
  DEFINE
  VAR __DS0FilterTable =
    TREATAS({"5AC"}, 'TBSdevice_mst'[ModelGroup])
  VAR __DS0FilterTable2 =
    FILTER(
      KEEPFILTERS(VALUES('TBSdevice_mst'[IsValidModel])),
      'TBSdevice_mst'[IsValidModel] = 1
    )
EVALUATE
  TOPN(
    501,
    SELECTCOLUMNS(
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(
            SUMMARIZECOLUMNS(
              'CallData'[call_prob_code],
              'TBSdevice_mst'[ModelGroup],
              __DS0FilterTable,
              __DS0FilterTable2,
              "CountRowsCallData", CALCULATE(COUNTROWS('CallData'))
            )
          ),
          OR(
            NOT(ISBLANK('CallData'[call_prob_code])),
            NOT(ISBLANK('TBSdevice_mst'[ModelGroup]))
          )
        )
      ),
      "'CallData'[call_prob_code]", 'CallData'[call_prob_code],
      "'TBSdevice_mst'[ModelGroup]", 'TBSdevice_mst'[ModelGroup]
    ),
    'CallData'[call_prob_code],
    1,
    'TBSdevice_mst'[ModelGroup],
    1
  )
ORDER BY
  'CallData'[call_prob_code], 'TBSdevice_mst'[ModelGroup]
0 REPLIES 0

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates