Forum Discussion
Paginated report freezing in the Validate Query Part
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
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,
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
Best Regards
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