Using Queries for Visualisations

[Anonymous]

Hi,

I'm wondering what I'm missing here, or if it's even possible. I'm trying to duplicate our current KPIs in Power BI. I have directly queried our Azure SQL database, using the query we currently use in SSMS. This queries multiple tables and fields to get the results needed. 

Inside Power BI, the query is created, and the results are correct, but I can't find a way to use that query for a visualisation. Is there any way to convert the query to a single field that I can then drag into the indicators? Or something along those lines? I've tried Googling but come up with nothing.

[HotChilli]

More information required.

You have to decide what you want to show on the visualisation.  For example, what do you use the current SSMS query to do?

KPI's are measurements towards a goal.  So decide what you are measuring and what the goal is.

[Anonymous]

So we're a software development company. We've built our own KPI dashboard already, using SQL queries, we just want to replicate it in Power BI. So I have the query saved, and it shows in my fields list. All the data is already manipulated how we want it to be, we just want to show the results of the query in a visualisation

[HotChilli]

I think you should start with the powerbi visualisation tutorials. This sort of thing..

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi

If you are replicating your existing visualisations then you have a head start. 

[Anonymous]

I looked at this, but the problem is that we use very complex queries, including multiple joins, and I don't think we're going to be able to use the builder to do what we want. I can't understand why you're able to enter a SQL query when you start pulling the data, if you're not able to view the results of the query in a visualisation

[HotChilli]

You'll have to post some sample data or a sample pbix because I don't really know what you mean.

Any data you have can be visualized in lots of different ways.  It's just a question of learning how to do it. 

[Anonymous]

In Power BI, I've used a query to ask our database how many of our paying customers have a company size of 1-5. The way the database was originally built means that this is not a simple couple of fields. You can see the list of fields that the query uses above. The results that the query produces is right - 30 paying companies of this size. 

I want a way to show that number 30 in a KPI visualisation. I've already got the query, I don't see why I can't just tick a box next to it like you can with the individual fields.

[HotChilli]

"The results that the query produces is right - 30 paying companies of this size"  Which of these fields contains the number 30?

or are you saying there are 30 rows in the table?

[Anonymous]

Hi 

Thanks for your help. I've figured out a workaround that we can just edit the SQL to include a COUNT field

[HotChilli · Accepted solution]

So 30 rows.  Of course you can change the sql query to count.

with your existing data though:

drag any field onto a card and set the field aggregation (small triangle in the Fields section of a card) to 'count'.

OR create a measure = COUNTROWS(yourTable) , drag that to a card

OR set 'default summarization' for a field to 'count', drag that to a card