Modeling data and relationships through the "PATH" field

[Anonymous]

Hello everyone,

I'm getting data from a bbdd oracle tables. The data comes from a proprietary application that relates investments, projects and programs through a "PATH" field. I will show you screenshots of the tables (investment portfolio and investment). In the first one, investment portfolios and their initiatives are defined. In the second table, all the investments that are not portfolio are defined (initiatives, projects and programs) and that through the "PATH" field, I must obtain the following results (screen results) in a PowerBI report.

I guess it would be better to model the data with M and then define a relationship, than do all the work with DAX. But I do not know where to start.

Thank you.

table "investment portfolio"

ID_PORTFOLIO	ACTIVE	NAME.PORTFOLIO	Id_INV	NAME
000001	true	Corporate strategic initiatives	102030	CSI001 - Digital Efficiency
000001	true	Corporate strategic initiatives	405060	CSI002 - Digital Security
000001	true	Corporate strategic initiatives	708090	CSI003 - Digital Challenge
000002	true	Risk investments	302010	RI001 - 5G
000002	true	Risk investments	605040	RI002 - WIFI or LIFI
000003	true	National investments	908070	NI001 - Digital transformation

table "investment"

ID_INV	NAME	%AWARD	PATH	TYPE OF INVESTMENT	BUDGET
102030	CSI001 - Digital Efficiency	0%	102030	Initiative	1000000
405060	CSI002 - Digital Security	0%	405060	Initiative	1000000
708090	CSI003 - Digital Challenge	0%	708090	Initiative	1000000
302010	RI001 - 5G	0%	302010	Initiative	1000000
605040	RI002 - WIFI or LIFI	0%	605040	Initiative	1000000
908070	NI001 - Digital transformation	0%	908070	Initiative	1000000
111111	PRY - Work environment	0%	908070 | 111111	Project	10000
111111	PRY - Work environment	100%	708090 | 111111	Project	10000
111111	PRY - Work environment	0%	302010 | 111111	Project	10000
222222	PGR - Digital culture	100%	708090 | 111111 | 222222	Program	50000

results "depending on the filter by portfolio, the results should be shown in the following tables"

Filter by Portfolio	Budget
Corporate strategic initiatives	3060000
Initiatives			table
CSI001 - Digital Efficiency	1000000
CSI002 - Digital Security	1000000
CSI003 - Digital Challenge	1060000
Proyects			table
PRY - Work environment	10000
Programs			table
PGR - Digital culture	50000

Filter by Portfolio	Budget
Risk investments	2000000
Initiatives			table
RI001 - 5G	1000000
RI002 - WIFI or LIFI	1000000
Proyects			table
PRY - Work environment	0
Programs			table
PGR - Digital culture	0

Filter by Portfolio	Budget
National investments	1000000
Initiatives			table
NI001 - Digital transformation	1000000
Proyects			table
PRY - Work environment	0
Programs			table
PGR - Digital culture	0

[v-qiuyu-msft]

Hi @Anonymous, 

In Query Editor, you can merge these two queries, then split the column PATH by delimiter "|", then group by the first part column generated from the PATH column to return total of Budget column. Then merge the investment portfolio to this new query. For more information, please check the attached pbix file. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Good morning,

I appreciate the effort. Surely I do not explain myself well. I need to create the relationship between them to be able to show 3 tables with the different levels of investment. In the first table show the initiatives, the second table shows the projects and in the third table the programs. There will be a filter with the investment portfolios, and depending on the portfolio you select, you should filter in the 3 tables the investments by their PATH ratio. And finally, what you have achieved, add the budget conditioned by the award.

Thank you anyway. I do not know if you could help me more.

A greeting.