Forum Discussion

amalrio's avatar
amalrio
Helper V
5 years ago

Dax Query Parameter in Query designer Paginated Reports

Hi Helpful people And Paginated Report Bear of Paginated Reports in Power BI with Chris Finlan and his Son 🙂

 

I am trying to set a parameter to my dax query into my Paginated report, It gives me headache, surly I must be doing someting incorrect.

 

below are the stpes I have taken to generate the Paginated report dataset, whic based on a PowerBI dataset Connection.

1) createa a PowerBI table on PowerBI desktop ( Just a flat table with all the fields I need on my Paginated Report)

2) Use Performance Analyzer to copy the DAX query

3) Add a Datset in Paginated using query designer and copy the Above generated DAX query.

 

Challange

I want to add filters into this dataset, I have been unsucessfully adding RSCustomDaxFilter into the DAX query itself as below

Below is the full DAX query generated by performance Analyzer with orange higilited text are the ones that I added into the script,  I get below error message

 

any help please..

======================

 

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Data_Q4'[PLANT_SUB_GROUP])),
NOT('Data_Q4'[PLANT_SUB_GROUP] IN {"OTHER"})
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Data_Q4'[AREA_GROUP]
,'Data_Q4'[TYPE]
,'Data_Q4'[PLANT_GROUP]
,'Data_Q4'[PLANT_SUB_GROUP]
,'Date'[YearMonthnumber]
,'Data_Q4'[SCENARIO_NAME]
,'Data_Q4'[START_DATE]
,'Date'[Date]
,'Data_Q4'[YEAR]
,__DS0FilterTable
,"SumMETRIC_VALUE", CALCULATE(SUM('Data_Q4'[METRIC_VALUE]))
//, RSCustomDaxFilter(@DataQ4SCENARIONAME,EqualToCondition,[Data_Q4].[SCENARIO_NAME],String)

//,RSCustomDaxFilter(@DataQ4YEAR,EqualToCondition,[Data_Q4].[YEAR],Double)

//,RSCustomDaxFilter(@DataQ4AREAGROUP,EqualToCondition,[Data_Q4].[AREA_GROUP],String)
)

VAR __DS0PrimaryWindowed =
TOPN(
500000,
__DS0Core,
'Data_Q4'[AREA_GROUP],
1,
'Data_Q4'[TYPE],
1,
'Data_Q4'[PLANT_GROUP],
1,
'Data_Q4'[PLANT_SUB_GROUP],
1,
'Date'[YearMonthnumber],
1,
'Data_Q4'[SCENARIO_NAME],
1,
'Data_Q4'[START_DATE],
1,
'Date'[Date],
1,
'Data_Q4'[YEAR],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'Data_Q4'[AREA_GROUP],
'Data_Q4'[TYPE],
'Data_Q4'[PLANT_GROUP],
'Data_Q4'[PLANT_SUB_GROUP],
'Date'[YearMonthnumber],
'Data_Q4'[SCENARIO_NAME],
'Data_Q4'[START_DATE],
'Date'[Date],
'Data_Q4'[YEAR]

9 Replies

  • amalrio , move filter after the summarized column

     

     

    DEFINE
    VAR __DS0FilterTable =
    FILTER(
    KEEPFILTERS(VALUES('Data_Q4'[PLANT_SUB_GROUP])),
    NOT('Data_Q4'[PLANT_SUB_GROUP] IN {"OTHER"})
    )

    VAR __DS0Core =
    SUMMARIZECOLUMNS(
    'Data_Q4'[AREA_GROUP]
    ,'Data_Q4'[TYPE]
    ,'Data_Q4'[PLANT_GROUP]
    ,'Data_Q4'[PLANT_SUB_GROUP]
    ,'Date'[YearMonthnumber]
    ,'Data_Q4'[SCENARIO_NAME]
    ,'Data_Q4'[START_DATE]
    ,'Date'[Date]
    ,'Data_Q4'[YEAR]

    ,"SumMETRIC_VALUE", CALCULATE(SUM('Data_Q4'[METRIC_VALUE]))

    ,__DS0FilterTable // at the end of summarizecolumns //https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax
    //, RSCustomDaxFilter(@DataQ4SCENARIONAME,EqualToCondition,[Data_Q4].[SCENARIO_NAME],String)

    //,RSCustomDaxFilter(@DataQ4YEAR,EqualToCondition,[Data_Q4].[YEAR],Double)

    //,RSCustomDaxFilter(@DataQ4AREAGROUP,EqualToCondition,[Data_Q4].[AREA_GROUP],String)
    )

    • amalrio's avatar
      amalrio
      Helper V

      It is after Summarize columns Champ.. am I missing a plot, my query above has the RSCustomDaxFilter at the end of the summarizie columns ??

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hey amalrio,

        Did you figure out ?

         

        Thanks

  • apologies it has been awhile, I think I ditched the filtering query and get a new dataset as report filter parameters. all those three RX filters I got them as sepereate datasets and use to setup as report filter