memory issues, errors when creating hierarchy using PATH & PATHITEM Functions

[Anonymous]

Hi everyone,

I have ran into an issue where Power BI is giving me multiple error messages, and I have now hit a brick wall here. I have tried the following with Power BI July release, and I am currently working with 2.38.4491.282 64-bit (August 2016).

Scenario
I want to apply security on my data set to make sure managers can only view their department. My data set consists of about 1100 rows, that I filtered to narrow down to around 8 rows, for testing reasons. In the data set, Employee Name and Supervisor Name. I have used these two columns to create a hierarchy using PATH() and PATHITEM() DAX functions. I created about 5 columns;
- 1 for storing the result of PATH(). This column stores the entire hierarchy, and with the 8 rows, it has about 4 levels
- Rest of the columns are for 4 levels of hierarchy
I have been able to create and test roles, and all works as expected.

The Problem
Problem begins once I start working with the entire data set of about 1100 rows. From the error message below, it looks like there's a code level bug, something to do with some pointer or something else. This is now appearing to be a crticial issue for my implementation of the business.

PBI Desktop error

Computer specs

I have flicked it to Power BI support but I am yet to receive a response that could help.

Thanks

Kaz

[Eric_Zhang]

@Anonymous

The function PATH() and PATHITEM() DAX functions work in my test, can you share the pbix so that I can try to reproduce the problem?

[Anonymous]

@Eric_Zhang

Thanks for the reply. In principal I do not have any trouble with Path and PathItem functions. They are working as expected for a dataset of about 10 rows. Problem starts when I try to generate the hierarchies for the entire dataset of about 1100 rows. 

In order to get you the PBIX, I'll have to skew the data, as this particular dataset has sensitive information. 

what was the dataset size in your test?

Thanks

Kaz

[Eric_Zhang]

@Anonymous

I test with 1500 rows, within which the deepest hierarchy level is 4 as in your case. However I don't get any error.

[Anonymous]

@Eric_Zhang

Thanks for the prompt reply.

How many potential levels do you have? I am starting to think that I am running into this particular error as there are more than X number of hierarchis that's upsetting Power BI Desktop. 

Thanks

Kaz

[Eric_Zhang]

@Anonymous

The test even works when there're 1500 levels. I doubt the error may be specific on your data?

[Anonymous]

@Eric_Zhang

Thanks for the reply Eric. 

This is interesting. I agree, it does look like this is issue is specific to my data set. 

What version of Power BI are you using? I have tried with July 2016 release, as well as August 2016 release. 

My computer specs are below ;

Computer Specs

One more thing, in your example, you have used numbers where as in my data I have actual name of the person. Do you think text length could be an issue here? Precisely the length of the column containing result of Path? It would result I would imagine in a quite lengthy field. I have looked up the documentation Data type limits and a community post over here.

I am lost ... your thoughts?

Thanks

Kaz

[Eric_Zhang]

@Anonymous

I'm using the lastest Aug's release. Regarding the limitation, I don't have any available data at hand to test.

I'm waiting for your sample to try to reproduce the problem.

[Anonymous]

@Eric_Zhang

Hi Eric, I did a test myself using Employee No & Manager No columns, I confirm I did not get any errors. This brings us back to the scenario where I have used text columns to generate the hierarchy and running into memory issues. 

Your thoughts on above?

Thanks

Kaz

[Eric_Zhang · Accepted solution]

---

@Anonymous wrote:

@Eric_Zhang

Hi Eric, I did a test myself using Employee No & Manager No columns, I confirm I did not get any errors. This brings us back to the scenario where I have used text columns to generate the hierarchy and running into memory issues. 

Your thoughts on above?

Thanks

Kaz

---

@Anonymous

It is not in my case. Could you share any sample that leads to the issue?

[Anonymous]

@Eric_Zhang

Thanks Eric, I am speechless! I tried generating a hierarchy using AdventureWorks by extracting the login ID out of HR.Employee table, works like a charm. Tried agian in my model, and again came across errors. 

I am going to compile a dataset of about 1100 dummy names, and then give it a try. Will share that with you.

Thanks

Kaz