Dax Parameters With Power Bi Report Builder

Power BI
1

I am trying to add parameters to a DAX query in Report Builder for Power Bi. I am using my query from Power Bi Desktop by copying the query with performance analyzer. I cannot get the filter/parameter to work and have tried multiple additions to the query any help is appreciated.

This is my query and I have tried adding

FILTER(VALUES(Date’[MonthInCalendar]),PATHCONTAINS (@MonthYear,Date’[MonthInCalendar])) in the query.

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
‘G_LEntries’[G_L_Account_No],
‘G_LEntries’[G_L_Account_Name],
‘Chart_of_Accounts’[Account_Category],
‘Chart_of_Accounts’[AccountCategoryIndex],
‘Chart_of_Accounts’[Account_Subcategory_Descript],
‘Date’[MonthInCalendar],
‘G_LEntries’[District],
“SumDebit_Amount”, CALCULATE(SUM(‘G_LEntries’[Debit_Amount])),
“SumCredit_Amount”, CALCULATE(SUM(‘G_LEntries’[Credit_Amount])),
“SumAmount”, CALCULATE(SUM(‘G_LEntries’[Amount]))

)

VAR __DS0PrimaryWindowed =

__DS0Core

EVALUATE
__DS0PrimaryWindowed

2

Hi @tessteam , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hi @tessteam, we’ve noticed that no response has been received from you since the 2nd of March. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hello @tessteam ,

I don’t know if you may already solved this problem, but struggling with a similar problem I have bumped into this post.
So, I will write my findings here, maybe someone will find it useful. :smile:

It seems that the trick is to have the parameter defined right inside the Query Designer window (not in the Parameters node):

DefineParameterInQueryDesigner

And don’t be “tempted” to use the Dimension and/or Hierarchy columns, just the parameter name and the default value

ParemeterValue
ParemeterValue1057×161 7.97 KB

And here is my copied DAX query from Performance Analyzer where I have modified only two things: deleted the TOPN section and used the defined parameter @UserEmail in the original filter formula:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({@UserEmail}, 'systemusers'[internalemailaddress])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'accounts'[Parent Account Name],
      'tasks'[activitytypecode],
      'incidents'[ticketnumber],
      'incidents'[title],
      'tasks'[subject],
      __DS0FilterTable,
      "Duration", 'tasks'[Duration],
      "Overtime", 'tasks'[Overtime],
      "TravelEffort", 'tasks'[TravelEffort]
    )

EVALUATE
  __DS0Core

ORDER BY
  'tasks'[scheduledstart] DESC,
  'accounts'[Parent Account Name]

Kind Regards,
Lucian

1 Like