Forum Discussion
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
- amitchandakSuper User
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)
)- amalrioHelper 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 ??
- AnonymousNot applicable
Hey amalrio,
Did you figure out ?
Thanks
- amalrioHelper V
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