Reply
New Member
Posts: 1
Registered: ‎01-14-2019

Data loading based on Table

[ Edited ]

Hallo fellow Power BI Users,

 

My Problem is that I want to design something like a template, where another user can select which data he wants to use and then build his report.

 

To make this problem clear i will give you an example:

 

Lets say we have a Table called KPI Meta table, consisting of ID, Name, Calculation Formula, ... In this Table all the Meta information of all the Users KPI are saved.

 

For each KPI the database has its own view, wich has a name like "KPI_VIEW_" &ID.. (KPI_VIEW_1, KPI_VIEW_2,...)

 

So based on the selected KPI we can just load the Data via

 

let

Source = FAKE.Database("fake.connection:76543", [Query="select * from FAKESCHEMA.KPI_VIEW_1"])

in

Source

 

What i want to archieve is, that when the user open this Power BI Desktop File he sees the KPI Meta Table, selects some IDs and then he clicks a Button and for each selected KPI a Query with the right Data is generated.

So the user can start to build his own report.

what I have tried so far:

* Create a parameterized Query. The problem is, that the user can only select 1 KPI, but the amount of Querys has to be variable

* Invoke this Query into the KPI Meta and expand the tables. Problem: I have a lot of KPIs and each Kpi View has its own Column Names, so i had around 20000 Columns afterward. I think it will be a little bit too messy for the user to select the right cols for the related KPI.

Thank you in advance and excuse my grammar, I'm not a native speaker

Markus