Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rdantasgadelha
New Member

Paginated report freezing in the Validate Query Part

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.

 

rdantasgadelha_0-1626192171551.png

 

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.

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

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], 01 )
        ),
        "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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft , 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,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft ,

 

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:

 

rdantasgadelha_2-1626342464812.png

 

Data Examples:

Dim_UMR_Names:

UMRUMRKey
UMR11
UMR22
UMR33
UMR44

 

Entity:

Entity NameEntity_KeyUMRKey

Entity1

11
Entity221
Entity332
Entity442
Entity553
Entity663
Entity774

 

Dim_IAM_Code

IAM_IDIAMKey

IAM_ID1

1
IAM_ID22

 

Revision_Well:

EntityKeyIAMKeyValue
11

0.5

21

0.35

32

0.7

42

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.

ParameterCaptionParameterValueParameterLevelIAM_ID
Blank() 0 
IAM_ID1IAM_ID11IAM_ID1

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors