Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 @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
Best Regards
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:
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 |
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |