topic Re: Dataset query error in report builder in Report Server

Dataset query error in report builder

Addams_Katherin — Tue, 31 Jan 2023 07:59:03 GMT

Hi ,

I am using report builder to connect to a power BI dataset. when I use the following DAX query, then it throws an error. does anyone know how to fix it?

EVALUATE VAR a = SUMMARIZECOLUMNS ( 'table1'[id], 'table2'[cid], RSCustomDaxFilter(@CDate,EqualToCondition,[CycleDate].[Date],DateTime), "MTD Sub ANP", [MTD Sub ANP], "Flag", 0, "Order",FORMAT(VALUE(MAX('CHANNEL'[Order])) , "#00.0") ) VAR b = SUMMARIZECOLUMNS ( 'table1'[type_id], 'table2'[cid], RSCustomDaxFilter(@CDate,EqualToCondition,[CycleDate].[Date],DateTime), "MTD Sub ANP", [MTD Sub ANP], "Flag", 0, "Order",FORMAT(4.5 , "#00.0"), FILTER(CHANNEL , CHANNEL[ID] in {5,6})) ） return UNION(a,b)

thanks.

Re: Dataset query error in report builder

jaweher899 — Tue, 31 Jan 2023 08:58:33 GMT

Here are some suggestions to help resolve the issue:

Ensure that the columns in the 'table1' and 'table2' referenced in the DAX query exist and are spelled correctly.
Verify that the RSCustomDaxFilter function is defined and its arguments are correct.
Ensure that the cdate variable is defined and its value is valid.
Ensure that the EqualToCondition in the RSCustomDaxFilter function is spelled correctly and used correctly.
Check that the [CycleDate].[Date] and DateTime used in the RSCustomDaxFilter function are defined and match the data type of the referenced data.
Check the syntax of the UNION function, making sure that the number and type of columns in each of the input tables (a and b) match.
Verify that the CHANNEL table used in the FILTER function is defined and its columns (e.g. ID) exist and are spelled correctly.

Can you please share some examples of data? can you put the error that you get?

Re: Dataset query error in report builder

Sahir_Maharaj — Tue, 31 Jan 2023 19:51:28 GMT

The error you're encountering might be due to a syntax error in the DAX code. It looks like there's an extra parenthesis at the end of the code.

Re: Dataset query error in report builder

Sahir_Maharaj — Tue, 31 Jan 2023 19:52:09 GMT

Here's the corrected code:

EVALUATE VAR a = SUMMARIZECOLUMNS ( 'table1'[id], 'table2'[cid], RSCustomDaxFilter(@CDate,EqualToCondition,[CycleDate].[Date],DateTime), "MTD Sub ANP", [MTD Sub ANP], "Flag", 0, "Order",FORMAT(VALUE(MAX('CHANNEL'[Order])), "#00.0") ) VAR b = SUMMARIZECOLUMNS ( 'table1'[type_id], 'table2'[cid], RSCustomDaxFilter(@CDate,EqualToCondition,[CycleDate].[Date],DateTime), "MTD Sub ANP", [MTD Sub ANP], "Flag", 0, "Order",FORMAT(4.5 , "#00.0"), FILTER(CHANNEL , CHANNEL[ID] in {5,6}) ) return UNION(a,b)

Re: Dataset query error in report builder

Sahir_Maharaj — Tue, 31 Jan 2023 19:52:44 GMT

If the error still persists, please provide more details about the error message so I can try to help you better.

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂