SQL Query SM in notebook

[Peter_23]

hi comunnity, i n this time, I try to write a query in SQL to SM by notebook, I know the "explore" option to get the matrix data, and "write DAX queries" are useful tools to query data, but my user it's more comfortable with SQL syntax.

So I decided to use notebook to query data from SM using SQL syntax. (sempy )  

The first question is: it its possible to query as example: ? to SM 🙂

%%sql
SELECT YEAR(OrderDate) AS OrderYear,
       SUM((UnitPrice * Quantity) + Tax) AS GrossRevenue
FROM salesorders
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;

The next step, I'm tryting to use the library, but dont work . 😞

The SM is a power bi dataset, so I'm using the sempy library. I have a test.

In the notebook. I have the next code.

import sempy.fabric as fabric

dataset = "SM-example matrix filtered by column" # Enter the name or ID of the semantic model
workspace = "TEst" # Enter the workspace name or ID in which the semantic model exists<p> <li-code lang="markup">fabric.list_measures(dataset)

so It's fine, but the next code, It trigger a expection.

# convert to a spark dataframe so that can use select syntax 
sparkdf = spark.createDataFrame(fabricdf)

ValueError: Some of types cannot be determined after inferring

Why the valueerror ? 😞

and the next code should be:

daxdf=sparkdf.select("Measure Name","Measure Expression").where(sparkdf["Measure Name"]=="Measure")

display(daxdf)

thanks in advance.

[v-lgarikapat · Accepted solution]

Hi @Peter_23 ,

Thanks for reaching out to the Microsoft fabric community forum

Can You Query SM with SQL Syntax?

Not directly in the way you're hoping. Semantic models (SMs) in Power BI are fundamentally DAX-based, not SQL based. Even though sempy lets you interact with SMs in notebooks, it doesn't support SQL querying like:

SELECT YEAR(OrderDate), SUM(...) FROM salesorders 

Instead, sempy is designed to:

• List metadata (tables, columns, measures)
• Execute DAX queries
• Retrieve results as pandas DataFrames

So your SQL Style query needs to be rewritten in DAX and executed via sempy's evaluate_dax() method.

 Why the ValueError on spark.createDataFrame(fabricdf)?

This error typically means that the fabricdf object you're passing to Spark doesn't have clearly inferrable types for all columns. Here's what might be going wrong:

• fabric.list_measures(dataset) returns a pandas DataFrame, not a Spark DataFrame.
• Spark needs explicit schema or cleanly inferrable types to convert a pandas DataFrame.

If some columns contain mixed types (e.g., None, strings, numbers), Spark can't infer them automatically.

How to Fix It

Option 1: Stick with pandas

If you're just filtering and displaying metadata, pandas is simpler and works fine:

python

import sempy.fabric as fabric

dataset = "SM-example matrix filtered by column"

workspace = "TEst"

fabricdf = fabric.list_measures(dataset)

Filter using pandas

daxdf = fabricdf[fabricdf["Measure Name"] == "Measure"]

display(daxdf)

Option 2: Explicitly define schema for Spark

If you must use Spark:

from pyspark.sql.types import StructType, StructField, StringType

schema = StructType([

    StructField("Measure Name", StringType(), True),

    StructField("Measure Expression", StringType(), True)

])

sparkdf = spark.createDataFrame(fabricdf, schema=schema)

daxdf = sparkdf.select("Measure Name", "Measure Expression").where(sparkdf["Measure Name"] == "Measure")

display(daxdf)

Semantic link propagation with SemPy - Microsoft Fabric | Microsoft Learn

SemPy in Microsoft Fabric: From SQL Scripts to Sem... - Microsoft Fabric Community

Best Regards,

Lakshmi.

[v-lgarikapat]

Hi @Peter_23 ,

I wanted to follow up and confirm whether you’ve had the opportunity to review the information we provided. If you have any questions or need further clarification, please don’t hesitate to reach out.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
Lakshmi.

[Peter_23]

Thanks @v-lgarikapat  I understand