Forum Discussion
Path and Matrix
[mPath] was created in your DAX calculated column or is that in your original dataset?
If it was in DAX, then why not continue with the article's example model?
If it is in your original dataset then I may not be able to assist further other than to say transform [mPath] to look like the article's model using a method like https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/ to create [lvl1], [lvl2], etc.I created a sample from scratch and followed the example model to produce:
from:
ID Parent_ID Employee Salary 1 2 Mike 3000 2 5 Sally 5000 3 2 Diane 3000 4 5 Randy 5000 5 Alan 6000 6 1 David 2000 7 4 Susan 3000 8 5 Carrie 4000 9 8 Luther 3000 10 5 Max 5000
lmilitz - In my opinion, using a pattern can be a great thing but sometimes it can cause you more grief if you don't understand the why it works and why it was made that way. While I have used this patterns techniques, none of my reporting has been exactly as the pattern.
The main thing I did was add an IF to each of your levels to check if there ISBLANK(Result). In those cases, return the Previous Level.
In doing that, you can just use SUM ( 'Sheet 2'[Sales] ). Notice though that it returns all of the sublevels and not just C & E. I also modified [Total Base] if you're really just wanting to use that style of measure.
In short. to accomplish the look/feel you're after you'll really have to come up with the logic that produces your desired output in that context. Probably the most difficult thing with DAX, again in my opinion.
This looks perfect! Thank you very much. The IF + ISBLANK() check in each level is definitely more sustainable than the pattern way. Thanks again!