Need some help mapping creating a Hierarchy with Child, Parent, Grandparent and Great Grandparent

[jansi79]

Hello, does anyone know how I can use Power Pivot to map out the hierarchy using the PATH function in Power Pivot? The table below illustrate the format of the data that I have. My dataset have about 1.000.000 records so I need an efficient function method to create this hierarchy..

<table border="1"><tr><td><b>Child</b></td><td><b>Parent</b></td><td><b>Grandparent</b></td><td><b>Great grandparent</b></td></tr>
<tr><td>1</td><td>3</td><td></td><td></td></tr>
<tr><td>2</td><td>6</td><td></td><td></td></tr>
<tr><td>3</td><td>10</td><td></td><td></td></tr>
<tr><td>4</td><td>8</td><td></td><td></td></tr>
<tr><td>5</td><td>4</td><td></td><td></td></tr>
<tr><td>6</td><td></td><td>13</td><td>18</td></tr>
<tr><td>7</td><td>18</td><td></td><td></td></tr>
<tr><td>8</td><td>10</td><td></td><td></td></tr>
<tr><td>9</td><td></td><td></td><td></td></tr>
<tr><td>10</td><td>18</td><td></td><td></td></tr>
<tr><td>11</td><td></td><td></td><td></td></tr>
<tr><td>12</td><td></td><td>13</td><td></td></tr>
<tr><td>13</td><td>18</td><td></td><td></td></tr>
<tr><td>14</td><td></td><td></td><td></td></tr>
<tr><td>15</td><td></td><td></td><td>18</td></tr>
<tr><td>16</td><td></td><td></td><td></td></tr>
<tr><td>17</td><td></td><td></td><td></td></tr>
<tr><td>18</td><td></td><td></td><td></td></tr>
<tr><td>19</td><td></td><td></td><td></td></tr>
<tr><td>20</td></tr></table>

 

[Matt Allington]

I just copied this. Parent-Child Hierarchies – DAX Patterns

 

[jansi79]

Thanks for the reply, but This only take into account the Parent-Child relationonship, When you run this functions you will not capture the relationship for Child 6 that does not have a Parent, but only have a Grandparent and Great Granparent.

Look at the table below

<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Child</th><th>Parent</th><th>Grandparent</th><th>Great grandparent</th><th>Path</th></tr></thead><tbody>
<tr><td>1</td><td>3</td><td> </td><td> </td><td>18|10|3|1</td></tr>
<tr><td>2</td><td>6</td><td> </td><td> </td><td>6|2</td></tr>
<tr><td>3</td><td>10</td><td> </td><td> </td><td>18|10|3</td></tr>
<tr><td>4</td><td>8</td><td> </td><td> </td><td>18|10|8|4</td></tr>
<tr><td>5</td><td>4</td><td> </td><td> </td><td>18|10|8|4|5</td></tr>
<tr><td>6</td><td> </td><td>13</td><td>18</td><td>6</td></tr>
<tr><td>7</td><td>18</td><td> </td><td> </td><td>18|7</td></tr>
<tr><td>8</td><td>10</td><td> </td><td> </td><td>18|10|8</td></tr>
<tr><td>9</td><td> </td><td> </td><td> </td><td>9</td></tr>
<tr><td>10</td><td>18</td><td> </td><td> </td><td>18|10</td></tr>
<tr><td>11</td><td> </td><td> </td><td> </td><td>11</td></tr>
<tr><td>12</td><td> </td><td>13</td><td> </td><td>12</td></tr>
<tr><td>13</td><td>18</td><td> </td><td> </td><td>18|13</td></tr>
<tr><td>14</td><td> </td><td> </td><td> </td><td>14</td></tr>
<tr><td>15</td><td> </td><td> </td><td>18</td><td>15</td></tr>
<tr><td>16</td><td> </td><td> </td><td> </td><td>16</td></tr>
<tr><td>17</td><td> </td><td> </td><td> </td><td>17</td></tr>
<tr><td>18</td><td> </td><td> </td><td> </td><td>18</td></tr>
<tr><td>19</td><td> </td><td> </td><td> </td><td>19</td></tr>
<tr><td>20</td><td> </td><td> </td><td> </td><td>20</td></tr>
</tbody></table>
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>