Forum Discussion
Query existing semantic model
There is a semantic model our IT department published and I need some data from it. However, I don't need to upload all the table into my report as it is transactions and it has high volume. I read about DAX queries and was wondering if there is a way to connect to the semantic model, XMLA endpoint, and just send a DAX query and retrieve the data I need only. Similar to sending a query to any other database. That way I only load what I need, the last two years of data.
If there is any other way to do this, more efficiently, I'd love to hear that as well.
Hello lg01
try these options
Use XMLA Endpoint + DAX Studio / SSMS
Connect using: powerbi://api.powerbi.com/v1.0/myorg/<WorkspaceName>
Run DAX queries like
EVALUATE FILTER('Sales', 'Sales'[Date] >= DATE(2023,1,1))
Export filtered results to CSV/Excel.
DirectQuery to Power BI Dataset
In Power BI Desktop: Home > Get Data > Power BI Datasets
Live connection; no data import.
DAX Query Tables (Hybrid)
Create custom table using filtered DAX query:
FilteredSales = FILTER('Sales', 'Sales'[Date] >= DATE(2023,1,1))
Requirement: Premium or PPU workspace + XMLA read access.
Thanks
If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
7 Replies
Hello lg01
try these options
Use XMLA Endpoint + DAX Studio / SSMS
Connect using: powerbi://api.powerbi.com/v1.0/myorg/<WorkspaceName>
Run DAX queries like
EVALUATE FILTER('Sales', 'Sales'[Date] >= DATE(2023,1,1))
Export filtered results to CSV/Excel.
DirectQuery to Power BI Dataset
In Power BI Desktop: Home > Get Data > Power BI Datasets
Live connection; no data import.
DAX Query Tables (Hybrid)
Create custom table using filtered DAX query:
FilteredSales = FILTER('Sales', 'Sales'[Date] >= DATE(2023,1,1))
Requirement: Premium or PPU workspace + XMLA read access.
Thanks
If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Thank you for the feedback. There is an option for "SQL Server Analysis Services database" that allows for DAX queries and import the data. Is that the equivalent of the first option you are describing?
Hi lg01 ,
Thank you for the accurate response pankajnamekar25 !Yes, the "SQL Server Analysis Services" connector in Power BI Desktop connects to the XMLA endpoint, which is exactly what Pankaj mentioned earlier. It lets you send DAX queries to the semantic model and import only the data you need just like querying an Analysis Services database.
You may need XMLA read permissions and a Premium or PPU workspace to use it this way.Refer the link for more information:https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-connect-tools
Happy to help you!
Thank you.
Regards,
Pallavi.
Hi lg01
Yes, you can efficiently query specific data from a published Power BI semantic model using the XMLA endpoint. This is exactly the scenario where XMLA shines, especially when you only need a filtered subset of data (like the last two years) without importing an entire table into your report.
Could you please try below steps:
- Get the XMLA endpoint URL from your Power BI workspace settings (Premium or PPU is required).
- Connect using “SQL Server Analysis Services” in Power BI Desktop, DAX Studio, or SSMS.
- Run a DAX query like:
DAX Code = EVALUATE FILTER('Sales', 'Sales'[Date] >= DATE(2023,1,1))
You can export the results to Excel/CSV or even use them in a live Power BI dataset (Live Connection, no data import needed). You’ll need your admin to enable it, or consider other options like Dataflows (though those require some extra setup and don’t offer the same flexibility).Hi lg01 ,
Just checking in — did the solution provided help resolve your issue?
If yes, please consider marking the solution that helped you as the accepted answer so it can help others facing a similar challenge.
Thank you.Hi lg01 ,
Following up to check whether you got a chance to review the suggestion given.If still facing any issue,feel free to reach out!
Thank you.Hi lg01 ,
I hope the suggested solution worked for you. If you found any workaround,please share it the community and accept your answer as solution.It will be helpful for others who are facing similar issue.If it is not resolved, please feel free to reach out!
Thank you.
