Forum Discussion
Hieararchical querry
Here are the picture :
in the SSMS , u can see that the parent_id number i put in the "where entity_id = " give me another table and it'll go until it reaches "parent_id = -1" , my request is i want to group all the big markets (who have parent_id = -1 , there are 4 of them , like they are markets and the others are submarkets)
ENtityTimesheet
Hello Anonymous
sorry, but I'm still not able to get a thing. I don't understand what here a relationship to the timesheet-table has to do
in PowerQuery you can Select rows as follows
let
Source = #table
(
{"ENTITY_ID","ENTITY_NAME","ENTITY_TYPE","PARENT_ID","ENTITY_CODE","ARCHIVID","MANAGER_ID"},
{
{"81","ALU","","150","","",""}, {"82","Test","","-1","","",""}, {"83","DELPHI","","30","","",""}
}
),
ChangedType = Table.TransformColumnTypes(Source,{{"PARENT_ID", type number}}),
FilteredRows = Table.SelectRows(ChangedType, each ([ENTITY_ID] = "82")and [PARENT_ID]=-1)
in
FilteredRows
If you want need it in Power BI then just connect to the two tables, connect them and create a filter where you filter for -1 and Entity ID
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
thank you for you answer , ill try mybest to be specific !
U asked about the relation in timesheet , timesheet has the "status" column wich i used to get a measure "global score" , but thats not important since i have the score now , the score is used to see if a market is validated or non validated , since we want to see the validation or non validation of an entity we need the entity table , ill post a picture so u can see the big market i need :
1 Project & run lead local
2 Project & run lead offshore
3 transverse (you guessed it they all have -1 in parent_id wich mean thet dont have a parent they are the top market and those top market have sub markets )
what i want here is to group all big markets with their sub market and try my score in them to see if they are validated or not (using the measure i did), how do i find their sub market , its in SSMS picture i upload , For exemple "select * from entity where entity_id=785" itll give me all the column with a parent_id = 31 with an entity name like "It Departement", if the second time i do the "select * from entity where entity_id=31" it ll give me a parent_id = 2 with an entity name "Apps" and same thing until it will reach parent_id = -1 , entity name = Project & run lead local
Conclusion :
Project & run lead local has a submarket wich is Apps and Apps has a submarket wich is IT DEPARTEMENT , i want to groupe all submarket and sub sub market of each entities who have parent_id = -1 (there are 4 of them the last is called Datacenter )