Table Self Relation / Hierarchy using the (.NET) API

[Anonymous]

Hi,

We are currently trying to implement Power BI to replace our current Reporting Tool and we almost have it all figured out, but have stumbled upon the following issue. Because of our complex application (and with that right checks) we did not want to link powerbi to our database, because that would require us to maintain another implementation of our rights. We managed to create datasets with the API and push data into this dataset, so the user that is requesting the report only gets to see his own data.

This is also what is causing all the problems right now, because we have managed to get tables with relationships into the dataset, with one exception, tables that have relations to themselfs. (So tables like organizations, where organizations can be part of another organization by linking them together with an ID and a ParentID.)

The reports that are currently being made on top of these datasets do not have the possibility to show metrics based on a full organization with all its sub organizations, because we cannot manage to link them together. Our datasets are generated at runtime and the reports are linked with the generated datasets using the powerbi frontend library so creating them manually is out of the option. We tried to figure this out with measurements and a calculated columns, but the calculated columns are not yet available through the API and also the creation of hierachies on tables also does not seem to be possible.

I was wondering if anyone possibly has a solution to our problem or any other kind of suggestion to fix or workaround this problem.

Thanks!

[lbendlin]

you can either use recursive functions in Power Query, or you can prepare the flattened versions of the hierarchies in your source.  Then in Power BI you can use the DAX functions PATH* to manipulate the resulting hierarchies.

If you provide some sample data and show the desired outcome we can help better.

[Anonymous]

I can definitely prepare some sample data, but what you described was exactly what we were trying to do. Except that the DAX PATH function seems to only work based on columns. So you cannot implement that as a measurement. I think the columns with DAX functions are called calculated columns (correct me if I am wrong), which we cannot generate with the API afaik.

So we could prepare a flattened version of the data on our end, that is not the problem. We only need a way to link them together so we can generate charts that have levels of hierarchy. Also this link should be possible to be done through the API or on a report level, because only the reports will be static, datasets will be generated at runtime so if the implementation needs to happen in the datasets we need it to be done through the API.