Forum Discussion

tanyatessteam's avatar
tanyatessteam
Frequent Visitor
5 years ago
Solved

Dax Filter/Parameters

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

ORDER BY
'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]

Power BI report builder
  • tanyatessteam's avatar
    tanyatessteam
    5 years ago

    Well it was not very technical!  I added the date to the data source and didn't use my date table so that I could build the query and parameter in Power BI Report Builder instead of using the DAX query from Power Bi desktop.  

4 Replies

  • tanyatessteam's avatar
    tanyatessteam
    Frequent Visitor
    5 years ago

    Unfortunately, these solutions did not work I just did it in another way.  Thanks for trying!

    Tanya

    • Icey's avatar
      Icey
      Community Support
      5 years ago

      Hi tanyatessteam ,

       

       

      I just did it in another way. 

       

      Could you share us your method?

       

       

      Best Regards,

      Icey

      • tanyatessteam's avatar
        tanyatessteam
        Frequent Visitor
        5 years ago

        Well it was not very technical!  I added the date to the data source and didn't use my date table so that I could build the query and parameter in Power BI Report Builder instead of using the DAX query from Power Bi desktop.  

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi tanyatessteam ,

     

    How about this?

    // DAX Query
DEFINE

VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES(Date'[MonthInCalendar])),
      PATHCONTAINS (@MonthYear,Date'[MonthInCalendar])))

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

ORDER BY
'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]

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.