SQL Query SM in notebook

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.