Dax Query Parameter in Query designer Paginated Reports

[amalrio]

Hi Helpful people And Paginated Report Bear of 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]

[amalrio]

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

[amitchandak]

@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]

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]

Hey amalrio,

Did you figure out ?

Thanks

[amalrio]

@Anonymous , Yes I manged.  🙂

[Anonymous]

Thats nice... Could you please post your solution .. 

I also need to add filter in Dax query.

This is my Dax query and I need filter on PL Group and Month as well

Thanks in advance .

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('LCS GL-Data'[PL Group], 'LCS GL-Data'[PL Item Description]), "IsGrandTotalRowTotal"
),
"Total_Actual", 'LCS GL-Data'[Total Actual],
"PreviousYearCalc", 'LCS GL-Data'[PreviousYearCalc],
"MTD_Variance", 'LCS GL-Data'[MTD Variance]
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'LCS GL-Data'[PL Group],
0,
'LCS GL-Data'[PL Item Description],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
'LCS GL-Data'[PL Group] DESC,
'LCS GL-Data'[PL Item Description]

[amalrio]

See below, You may have to compare two things to spot the difference as of now I do not remember what I did 🙂

// 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]))

)

VAR __DS0PrimaryWindowed =
TOPN(
10000000,
__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]

[Anonymous]

Unfortunately you both queries look same😄..!