KPI using two different data sources

[mcody]

Hi,

I would like to display KPI on dashboard.

Actual value comes from on-premises SQL.

Target/Goal is stored on another system, excel file.

Now I can create two different datasets and make separte reports for actual and target, then use these to add two seperate live tiles to dashboard. But, would really like to combine and use one KPI visual.

Is there any way to achieve this?

Thanks.

[v-qiuyu-msft · Accepted solution]

Hi @mcody,

You can open Power BI desktop, get data from SQL Server database and Excel file, create proper relationships between actual and target tables, you can go through this article firstly: Create and manage relationships in Power BI Desktop. Then you can create a KPI visual use data from those two data sources.

After publish the report to Power BI service, configure the dataset to use gateway, you can pin the visual from the report to a dashboard.

Best Regards,
QiuyunYu

[mcody]

Hi,

I didn't explain full situation in post.

SQL DB using Direct Query mode, so cannot load data from Excel file without changing mode. Not allowed to mix modes it seems.

So I guess my options are:

• Use import mode for all (Reason for selecting Direct Query was to use auto schedule refresh, not available in import mode)
• Pipe Excel into SQL and take it from there

Unless you have some other suggestion.

thanks.