Forum Discussion
cell magic %%dax returning error
I have a notebook thats using Semantic Link to get data from a Power BI semantic model. I am able to successfully access data using fabric.evaluate_measure, however the limitation of the filter only supporting the in operator, has lead me to explore
fabric.evaluate_dax and %%dax cell magic but these return errors. I'm more interested in using %%dax as this would allow me to use parameters that I have in the notebook with the DAX expression.
When I run the following:
%%dax "REDACTED" -w "REDACTED"
EVALUATE
SUMMARIZECOLUMNS (
'dim_date'[calendar_start_of_week],
'dim_ga_session'[ga_page_path_session_landing],
'dim_ga_session'[ga_is_login_session],
'dim_ga_session'[ga_channel],
'dim_ga_session'[ga_site_variant],
FILTER (
KEEPFILTERS (
VALUES ( 'dim_date'[calendar_start_of_week] )
),
'dim_date'[calendar_start_of_week]
>= DATE ( 2024, 1, 1 )
),
'dim_ga_session'[ga_site_variant]
),
"sessions", 'model_metrics'[# Sessions],
"bounce_sessions", 'model_metrics'[# Bounce Sessions]
)
I get the error:
---------------------------------------------------------------------------
AdomdErrorResponseException Traceback (most recent call last)
AdomdErrorResponseException: Query (16, 6) The syntax for '<ccon>,</ccon>' is incorrect. (<ccon>EVALUATE
SUMMARIZECOLUMNS (
'dim_date'[calendar_start_of_week],
'dim_ga_session'[ga_page_path_session_landing],
'dim_ga_session'[ga_is_login_session],
'dim_ga_session'[ga_channel],
'dim_ga_session'[ga_site_variant],
FILTER (
KEEPFILTERS (
VALUES ( 'dim_date'[calendar_start_of_week] )
),
'dim_date'[calendar_start_of_week]
>= DATE ( 2024, 1, 1 )
),
'dim_ga_session'[ga_site_variant]
),
"sessions", 'model_metrics'[# Sessions],
"bounce_sessions", 'model_metrics'[# Bounce Sessions]
)
</ccon>).
Technical Details:
RootActivityId: e3a15cb7-c090-4074-bd51-561af95fd7eb
Date (UTC): 1/11/2024 12:41:53 PM
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.ExecuteStatement(String statement, IDictionary connectionProperties, IDictionary commandProperties, IDataParameterCollection parameters, Boolean isMdx)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader()
at Microsoft.Fabric.SemanticLink.ParquetWriter.WriteAsync()
The above exception was the direct cause of the following exception:
AggregateException Traceback (most recent call last)
Cell In[13], line 1
----> 1 get_ipython().run_cell_magic('dax', '"REDACTED" -w "REDACTED"', 'EVALUATE\nSUMMARIZECOLUMNS (\n \'dim_date\'[calendar_start_of_week],\n \'dim_ga_session\'[ga_page_path_session_landing],\n \'dim_ga_session\'[ga_is_login_session],\n \'dim_ga_session\'[ga_channel],\n \'dim_ga_session\'[ga_site_variant],\n FILTER (\n KEEPFILTERS (\n VALUES ( \'dim_date\'[calendar_start_of_week] )\n ),\n \'dim_date\'[calendar_start_of_week]\n >= DATE ( 2024, 1, 1 )\n ),\n \'dim_ga_session\'[ga_site_variant]\n ),\n "sessions", \'model_metrics\'[# Sessions],\n "bounce_sessions", \'model_metrics\'[# Bounce Sessions]\n)\n')
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/IPython/core/interactiveshell.py:2478, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
2476 with self.builtin_trap:
2477 args = (magic_arg_s, cell)
-> 2478 result = fn(*args, **kwargs)
2480 # The code below prevents the output from being displayed
2481 # when using magics with decodator @output_can_be_silenced
2482 # when the last Python token in the expression is a ';'.
2483 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_daxmagics.py:44, in DAXMagics.dax(self, line, cell)
41 cell = cell.format(**self.shell.user_ns) # type: ignore[attr-defined]
43 # evaluate the dax
---> 44 df = fabric.evaluate_dax(dataset, cell, workspace=workspace)
46 # store in output variable (following https://ipython.readthedocs.io/en/stable/interactive/magics.html#cellmagic-script)
47 if args.output:
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/_utils/_log.py:269, in mds_log.<locals>.get_wrapper.<locals>.log_decorator_wrapper(*args, **kwargs)
266 raise
268 try:
--> 269 result = func(*args, **kwargs)
271 # The invocation for get_message_dict moves after the function
272 # so it can access the state after the method call
273 message.update(extractor.get_completion_message_dict(result, arg_dict))
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_flat.py:891, in evaluate_dax(dataset, dax_string, workspace, verbose)
869 """
870 Compute `DAX <https://learn.microsoft.com/en-us/dax/>`_ query for a given dataset.
871
(...)
888 :class:`~sempy.fabric.FabricDataFrame` holding the result of the DAX query.
889 """
890 client: DatasetXmlaClient = _get_or_create_workspace_client(workspace).get_dataset_client(dataset, mode=ConnectionMode.XMLA) # type: ignore
--> 891 return client.evaluate_dax(dax_string, verbose)
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_client/_base_dataset_client.py:103, in BaseDatasetClient.evaluate_dax(self, query, verbose)
87 def evaluate_dax(self, query: str, verbose: int = 0) -> FabricDataFrame:
88 """
89 Retrieve results of DAX query as a FabricDataFrame.
90
(...)
101 FabricDataFrame converted from the results of a DAX query.
102 """
--> 103 df = self._evaluate_dax(query, verbose)
104 return FabricDataFrame(df, dataset=self._dataset_name, workspace=self._workspace_client.get_workspace_name())
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_client/_dataset_xmla_client.py:68, in DatasetXmlaClient._evaluate_dax(self, query, verbose, batch_size)
67 def _evaluate_dax(self, query: str, verbose: int = 0, batch_size: int = 100000) -> pd.DataFrame:
---> 68 return self._get_DAX(dax_string=query, batch_size=batch_size, verbose=verbose)
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/_utils/_log.py:269, in mds_log.<locals>.get_wrapper.<locals>.log_decorator_wrapper(*args, **kwargs)
266 raise
268 try:
--> 269 result = func(*args, **kwargs)
271 # The invocation for get_message_dict moves after the function
272 # so it can access the state after the method call
273 message.update(extractor.get_completion_message_dict(result, arg_dict))
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_client/_dataset_xmla_client.py:195, in DatasetXmlaClient._get_DAX(self, dax_string, batch_size, verbose)
193 df, fields = dax_to_parquet()
194 else:
--> 195 raise e
197 return self._convert_dtypes(df, fields)
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_client/_dataset_xmla_client.py:185, in DatasetXmlaClient._get_DAX(self, dax_string, batch_size, verbose)
182 pass
184 try:
--> 185 df, fields = dax_to_parquet()
186 except AdomdConnectionException:
187 # retry on connection error - examples include "The connection either timed out or was lost." or "The request was routed to the wrong node"
188 df, fields = dax_to_parquet()
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/_utils/_log.py:269, in mds_log.<locals>.get_wrapper.<locals>.log_decorator_wrapper(*args, **kwargs)
266 raise
268 try:
--> 269 result = func(*args, **kwargs)
271 # The invocation for get_message_dict moves after the function
272 # so it can access the state after the method call
273 message.update(extractor.get_completion_message_dict(result, arg_dict))
File ~/cluster-env/clonedenv/lib/python3.10/site-packages/sempy/fabric/_client/_dataset_xmla_client.py:168, in DatasetXmlaClient._get_DAX.<locals>.dax_to_parquet()
166 try:
167 with self.get_adomd_connection() as adomd_connection:
--> 168 fields = ParquetWriter.DAXToParquet(
169 dax_string,
170 temp_file_name,
171 batch_size,
172 adomd_connection,
173 verbose
174 )
175 df = pd.read_parquet(temp_file_name)
176 return df, fields
AggregateException: One or more errors occurred. (Query (16, 6) The syntax for '<ccon>,</ccon>' is incorrect. (<ccon>EVALUATE
SUMMARIZECOLUMNS (
'dim_date'[calendar_start_of_week],
'dim_ga_session'[ga_page_path_session_landing],
'dim_ga_session'[ga_is_login_session],
'dim_ga_session'[ga_channel],
'dim_ga_session'[ga_site_variant],
FILTER (
KEEPFILTERS (
VALUES ( 'dim_date'[calendar_start_of_week] )
),
'dim_date'[calendar_start_of_week]
>= DATE ( 2024, 1, 1 )
),
'dim_ga_session'[ga_site_variant]
),
"sessions", 'model_metrics'[# Sessions],
"bounce_sessions", 'model_metrics'[# Bounce Sessions]
)
</ccon>).
Technical Details:
RootActivityId: e3a15cb7-c090-4074-bd51-561af95fd7eb
Date (UTC): 1/11/2024 12:41:53 PM)
---> Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (16, 6) The syntax for '<ccon>,</ccon>' is incorrect. (<ccon>EVALUATE
SUMMARIZECOLUMNS (
'dim_date'[calendar_start_of_week],
'dim_ga_session'[ga_page_path_session_landing],
'dim_ga_session'[ga_is_login_session],
'dim_ga_session'[ga_channel],
'dim_ga_session'[ga_site_variant],
FILTER (
KEEPFILTERS (
VALUES ( 'dim_date'[calendar_start_of_week] )
),
'dim_date'[calendar_start_of_week]
>= DATE ( 2024, 1, 1 )
),
'dim_ga_session'[ga_site_variant]
),
"sessions", 'model_metrics'[# Sessions],
"bounce_sessions", 'model_metrics'[# Bounce Sessions]
)
</ccon>).
Technical Details:
RootActivityId: e3a15cb7-c090-4074-bd51-561af95fd7eb
Date (UTC): 1/11/2024 12:41:53 PM
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.ExecuteStatement(String statement, IDictionary connectionProperties, IDictionary commandProperties, IDataParameterCollection parameters, Boolean isMdx)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader()
at Microsoft.Fabric.SemanticLink.ParquetWriter.WriteAsync()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
at System.Threading.Tasks.Task`1.get_Result()
at Microsoft.Fabric.SemanticLink.ParquetWriter.DAXToParquet(String dax, String fileName, Int32 batchSize, AdomdConnection adomdConnection, Int32 verbose)
What am I missing here?
9 Replies
Hi mark_carlisle ,
Thanks for using Fabric Community.
Let me check and try to find out if there is any syntax issue with your code.Hi Anonymous - any progress we're still trying but with no success.
Hi mark_carlisle ,
I can find some issue with your syntax, would like to understand what you are trying to do.As I understand you are trying to apply filters greater than particular date.
For that you can try to use syntax - For eg:
import sempy.fabric as fabric df_dax = fabric.evaluate_dax( "Customer Profitability Sample", """ EVALUATE SUMMARIZECOLUMNS( 'State'[Region], 'Calendar'[Year], 'Calendar'[Month], 'State'[State], FILTER('Calendar', 'Calendar'[Year] >= 2009 && 'Calendar'[Year] <= 2011), ) """) df_dax
Inorder to get some idea please refer below link,
Read data from semantic models and write data that semantic models can consume - Microsoft Fabric | Microsoft Learn
FILTER function (DAX) - DAX | Microsoft Learn
Hope this is helpful. Please let me know incase of further queries.
