SQL dynamic filter (relation by sql query)

[luisfgfurtado]

I'm trying to use Power BI to make some reports for our Redmine. The Redmine uses MySQL database.
I already connected PowerBi direct to MySQL database and imported all tables that I need.
The problem is the way of Redmine relates projects and sub projects. All the projects are in the same table and two fields "lft" and "rgt" are used to determine the position of projects in a very large tree.
When I need to filter all children projects of a parent project, I use:

SELECT
 *
FROM
 (
 SELECT * FROM `projects`
 WHERE `identifier` like 'aprimora-ef'
 ) as mp
 INNER JOIN `projects` p on p.`lft` BETWEEN mp.`lft` AND mp.`rgt`

Have some way to include this kind of filter in PowerBI?
I would like to display a list of some projecs and a table filtered by selected project including a total of issues of the selected

project and their subprojects.

thanks

[Greg_Deckler · Accepted solution]

Not sure I fully understand your issue but will take a whack at it. You can relate tables to one another, so if you have a project table, you could import it twice (the second would be for relating to sub-projects), you could then have a slicer for project identifiers and it would slice a table that would display your sub-projects and issues.

Some example data and desired output might help clarify things.

[assamaral]

How did you make the connection? Can you send an example please? Thanks.