Paginated report freezing in the Validate Query Part

Question

Helo everybody,&nbsp;I am trying to build a dataset query for a parameter, but the report builder freezes after I click Validate Query. See picture below.&nbsp;&nbsp;The query that I used in the dataset is the one below:&nbsp;&nbsp;&nbsp;&nbsp;DEFINE
	VAR _IAMKey =
		CALCULATETABLE(
			VALUES(Revision_Well[IAMKey]),
			RSCustomDaxFilter(@DimUMRNamesUMR,EqualToCondition,[Dim_UMR_Names].[UMR],String)
		)

EVALUATE 
CALCULATETABLE(
	SELECTCOLUMNS(
		ADDCOLUMNS(
			SUMMARIZECOLUMNS(
				ROLLUPADDISSUBTOTAL('Dim_IAM_Code'[IAM_ID], "h0")
			), 
			"ParameterLevel", if ([h0], 0, 1)
		), 
		"ParameterCaption", SWITCH([ParameterLevel], 1, "" &amp; 'Dim_IAM_Code'[IAM_ID], "Blank()"), 
		"ParameterValue", "" &amp; 'Dim_IAM_Code'[IAM_ID], 
		"ParameterLevel", [ParameterLevel], 
		"'Dim_IAM_Code'[IAM_ID]", 'Dim_IAM_Code'[IAM_ID]
		),
		TREATAS( _IAMKEY, Dim_IAM_Code[IAMKey])
	) order by 'Dim_IAM_Code'[IAM_ID], [ParameterLevel]&nbsp;&nbsp;&nbsp;&nbsp;Also, I have checked in DaxStudio and everything works fine, with the following query:&nbsp;&nbsp;&nbsp;&nbsp;DEFINE
	VAR _IAMKey =
		CALCULATETABLE(
			VALUES(Revision_Well[IAMKey]),
			FILTER(Dim_UMR_Names, Dim_UMR_Names[UMR] = "Duvernay")
//			RSCustomDaxFilter(@DimUMRNamesUMR,EqualToCondition,[Dim_UMR_Names].[UMR],String)
		)

EVALUATE 
CALCULATETABLE(
	SELECTCOLUMNS(
		ADDCOLUMNS(
			SUMMARIZECOLUMNS(
				ROLLUPADDISSUBTOTAL('Dim_IAM_Code'[IAM_ID], "h0")
			), 
			"ParameterLevel", if ([h0], 0, 1)
		), 
		"ParameterCaption", SWITCH([ParameterLevel], 1, "" &amp; 'Dim_IAM_Code'[IAM_ID], "Blank()"), 
		"ParameterValue", "" &amp; 'Dim_IAM_Code'[IAM_ID], 
		"ParameterLevel", [ParameterLevel], 
		"'Dim_IAM_Code'[IAM_ID]", 'Dim_IAM_Code'[IAM_ID]
		),
		TREATAS( _IAMKEY, Dim_IAM_Code[IAMKey])
	) order by 'Dim_IAM_Code'[IAM_ID], [ParameterLevel]&nbsp;&nbsp;&nbsp;&nbsp;Please help.

anonymous · Answer

Hi&nbsp;rdantasgadelha&nbsp;,
Compare the query in pagination report query textarea and the query in DaxStudio, find that the query in pagination report query textarea has one more condition (the red font part in the below table), which may be the cause of report builder freezes. Please try to remove it or change it with proper filter condition, later check whether it can work well after clicked&nbsp;Validate Query button.

DEFINE&nbsp;&nbsp;&nbsp;&nbsp;VAR&nbsp;_IAMKey&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATETABLE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Revision_Well[IAMKey]&nbsp;)&nbsp;,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RSCustomDaxFilter(@DimUMRNamesUMR,EqualToCondition,[Dim_UMR_Names].[UMR],String)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)EVALUATECALCULATETABLE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SELECTCOLUMNS&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZECOLUMNS&nbsp;(&nbsp;ROLLUPADDISSUBTOTAL&nbsp;(&nbsp;'Dim_IAM_Code'[IAM_ID],&nbsp;"h0"&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ParameterLevel",&nbsp;IF&nbsp;(&nbsp;[h0],&nbsp;0,&nbsp;1&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ParameterCaption",&nbsp;SWITCH&nbsp;(&nbsp;[ParameterLevel],&nbsp;1,&nbsp;""&nbsp;&amp;&nbsp;'Dim_IAM_Code'[IAM_ID],&nbsp;"Blank()"&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ParameterValue",&nbsp;""&nbsp;&amp;&nbsp;'Dim_IAM_Code'[IAM_ID],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ParameterLevel",&nbsp;[ParameterLevel],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"'Dim_IAM_Code'[IAM_ID]",&nbsp;'Dim_IAM_Code'[IAM_ID]&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;TREATAS&nbsp;(&nbsp;_IAMKEY,&nbsp;Dim_IAM_Code[IAMKey]&nbsp;))ORDER&nbsp;BY&nbsp;&nbsp;&nbsp;&nbsp;'Dim_IAM_Code'[IAM_ID],&nbsp;&nbsp;&nbsp;&nbsp;[ParameterLevel]

Best Regards

rdantasgadelha · Answer

Hi&nbsp;Anonymous&nbsp;, thank you for your answer. Acctually, I need to have&nbsp;the line you mention in filter the query based on the Report Builder paramater&nbsp;@DimUMRNamesUMR. this line was generated automatically by the Report Builder, I just change the placement.&nbsp;Is this case, what would you say is a proper filter condition?&nbsp;Cheers,

anonymous · Answer

Hi&nbsp;rdantasgadelha&nbsp;,
Could you please explain the backend logic of query and the expected return result with exact data? Also please provide some sample data in table&nbsp;Revision_Well and&nbsp;Dim_IAM_Code.
1. Sample data
Revision_Well

Col1
Col2
Col3

aa
bb
cc

xx
gg
dd

Dim_IAM_Code

Col1
Col2
Col3

hh
jj
kk

ll
uu
pp

2. Backend logic&nbsp;
Fulfill conditions: 1)&nbsp;Dim_IAM_Code'[IAM_ID] in {"xx","xx"}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2) if (a=xx,1,0)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ...
3. Expected result

Col1
Col2
Col3

xx
xx
xx

In addition,&nbsp; you can refer the content in the following links to modify your query.
Power BI Report Builder And RSCustomDaxFilter
With ROLLUPADDISSUBTOTAL
Best Regards

rdantasgadelha · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;I have prepared the data you requested. But it would be interesting to know my particular sintax is not working. I am not sure if it is because of the PowerBI report builder, but I don´t see why it should´t work.&nbsp;Find below the relationships between tables in the data model:&nbsp;&nbsp;Data Examples:Dim_UMR_Names:UMRUMRKeyUMR11UMR22UMR33UMR44&nbsp;Entity:Entity NameEntity_KeyUMRKeyEntity111Entity221Entity332Entity442Entity553Entity663Entity774&nbsp;Dim_IAM_CodeIAM_IDIAMKeyIAM_ID11IAM_ID22&nbsp;Revision_Well:EntityKeyIAMKeyValue110.5210.35320.7420.8&nbsp;I defined a parameter in the paginated report called&nbsp;@DimUMRNamesUMR. When I set the parameter to UMR1, I wish to get the result below. But also, I wish to select multiple parameters (UMR1 and UMR2) for example.ParameterCaptionParameterValueParameterLevelIAM_IDBlank()&nbsp;0&nbsp;IAM_ID1IAM_ID11IAM_ID1&nbsp;

Forum Discussion

Paginated report freezing in the Validate Query Part

4 Replies

Related content

Paginated Report

Theme Validation Error

Freeze top header

Paginated report

Validation/optimisation options for AI features