There's surely a more efficient way of handling this than how I am trying. I'm hoping to get some guidance please.
I have four tables of 'entities'. One for each of four levels, with parent / child in each.
- function (great-grandparent; 40 rows)
- subfunction (grandparent; 85 rows)
- process (parent; 580 rows)
- requirement (child; 11,000 rows)
I have another four tables of 'selection' data (one for each of the above levels). A 'selection' is a set of 'entities' (e.g. 2,500 requirements, and whatever their parents, grandparent etc might be). The tables list the entity and selection numbers. Combined there is about 475k rows.
Now, each table of entities has a sort value of 0-100 attributed to it. So the sorting data I am provided only relates to that entity's level. But, I eventually need to display each 'selection' as the set of entities in their heirarchy, and conversely, be able to 'browse' the heirarchies to be able to select entities to create a selection in the first place.
I'm currently using two xViz Hierarchy Filters. The first I can select a 'Selection' set, which limits the second filter to displaying the entities to check and uncheck from there.
I then want to output a table reflecting these selections which is where I get stuck, as the sort values are not valid when you combine the tables.
What I'm currently doing is transforming the data in Power Query before bringing it in to Power BI (i was getting circular reference issues with the avenues I tried in DAX). I'm trying to calculate an overall heirarchy/sort value 'all_sort' by...
1/ For highest level (functions): Sorting, indexing, then multiplying the index value by 100000000000 to get 'all_sort'.
2/ Level 2 (subfunction): Sorting, indexing, multiplying its index value by 100000000, then indexing and returning its parent's (function) 'all_sort' value and adding them to get its own 'all_sort' value.
3/ Level 3 (process): Sorting, indexing, multiplying its index value by 100000, then indexing and returning its parent's (subfunction) 'all_sort' value and adding them to get its own 'all_sort' value.
4/ Level 4 (requirement): Adding its sort value (it's ID) to its parent's 'all_sort' value.
It's taking about 6 minutes to load the queries.
Surely there's a better way to approach this?
Example raw data showing the parent/child relationship, and the sort data:
tbl_functions - Entity numbers 1-100
tbl_sub_function - Entity numbers 101-300
| SF No. | F No. | Sort |
| 101 | 1 | 1 |
| 102 | 1 | 2 |
tbl_process - Entity numbers 301-999
| P No. | SF No. | Sort |
| 301 | 101 | 5.0 |
| 302 | 102 | 98.0 |
| 303 | 102 | 6.0 |
tbl_requirements - Entity numbers 1000+
| R No. | P No. |
| 1000 | 301 |
| 1001 | 301 |
| 1002 | 302 |
| 1003 | 302 |
| 1004 | 302 |
| 1005 | 303 |
This is how I need to display a 'selection', in its sorted heirarchy. I've noted the sort value from its original table to show that it can't be used in this raw form:
| Entity | Sort |
| F | 2 | 4 |
| F | 1 | 14 |
| SF | 101 | 1 |
| P | 301 | 5 |
| R | 1000 | |
| R | 1001 | |
| SF | 102 | 2 |
| P | 303 | 6 |
| R | 1005 | |
| P | 302 | 98 |
| R | 1002 | |
| R | 1003 | |
| R | 1004 | |
