Paginated report freezing in the Validate Query Part

[rdantasgadelha]

Helo everybody,

I am trying to build a dataset query for a parameter, but the report builder freezes after I click Validate Query. See picture below.

The query that I used in the dataset is the one below:

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, "" & 'Dim_IAM_Code'[IAM_ID], "Blank()"), 
		"ParameterValue", "" & '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]

Also, I have checked in DaxStudio and everything works fine, with the following query:

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, "" & 'Dim_IAM_Code'[IAM_ID], "Blank()"), 
		"ParameterValue", "" & '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]

Please help.

[Anonymous]

Hi @rdantasgadelha ,

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 Validate Query button.

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, "" & 'Dim_IAM_Code'[IAM_ID], "Blank()" ),
        "ParameterValue", "" & '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]

Best Regards

[rdantasgadelha]

Hi @Anonymous , thank you for your answer. Acctually, I need to have the line you mention in filter the query based on the Report Builder paramater @DimUMRNamesUMR. this line was generated automatically by the Report Builder, I just change the placement.

Is this case, what would you say is a proper filter condition?

Cheers,

[Anonymous]

Hi @rdantasgadelha ,

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 Revision_Well and 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 

Fulfill conditions: 1) Dim_IAM_Code'[IAM_ID] in {"xx","xx"}

                            2) if (a=xx,1,0)

                            ...

3. Expected result

Col1	Col2	Col3
xx	xx	xx

In addition,  you can refer the content in the following links to modify your query.

Power BI Report Builder And RSCustomDaxFilter

With ROLLUPADDISSUBTOTAL

Best Regards

[rdantasgadelha]

Hi @Anonymous ,

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.

Find below the relationships between tables in the data model:

Data Examples:

Dim_UMR_Names:

UMR	UMRKey
UMR1	1
UMR2	2
UMR3	3
UMR4	4

Entity:

Entity Name	Entity_Key	UMRKey
Entity1	1	1
Entity2	2	1
Entity3	3	2
Entity4	4	2
Entity5	5	3
Entity6	6	3
Entity7	7	4

Dim_IAM_Code

IAM_ID	IAMKey
IAM_ID1	1
IAM_ID2	2

Revision_Well:

EntityKey	IAMKey	Value
1	1	0.5
2	1	0.35
3	2	0.7
4	2	0.8

I defined a parameter in the paginated report called @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.

ParameterCaption	ParameterValue	ParameterLevel	IAM_ID
Blank()		0
IAM_ID1	IAM_ID1	1	IAM_ID1