queries as basis for reports / Save query as a view

[Josef2]

Dear all,

is it possible to use SQL-queries as a basis for reports?

We have prepared some which we would like to use similar to tables as basis for reports/visuals etc.

Does anyone know how to do?

It has been announced as feature Datamarts DML / DDL (under review): Save query as view (coming soon)” https://powerbi.microsoft.com/en-us/blog/power-bi-datamart-january-2023-feature-summary/

Many thanks in advance.

Kind rgds

Josef

[Martin_D]

Hi @Josef2 ,

In the advanced settings of the SQL Server database connector you can use your SQL query as a basis for a report as per your requirement.

References:

Advanced Options: Getting Data into Power BI from SQL Server | by Mubar Dauda | Microsoft Power BI |...

Power Query SQL Server connector - Power Query | Microsoft Learn

Kind regards,

Martin

[Josef2]

Hi @Martin_D ,

many thanks for your quick answer.

What I did not mention is that we receive the data from Jira via PowerBI-Connector.
We want to define some SQL queries ourselves and would like to know if there is a possibility to use the queries (their results) similar to the tables as basis for reports/visuals..?

Many thanks in advance.

kind rgds

Josef

[Martin_D]

Hi @Josef2 ,

The query language for Power BI data models is DAX, not SQL. You can express the same query in DAX. And you can load the result of this DAX query as a calculated table to the data model. Then the result of the query becomes part of the data model and you can use it in visuals.
Here are a few ideas to get started with translating your SQL into DAX: From SQL to DAX: Joining Tables - SQLBI
Kind regards,
Martin