Power BI - Dynamic Hierarchies

Hey guys! Parker here. In this Power BI tutorial, I show you how to set up dynamic hierarchies! This allows you to specify the different levels of the hierarchy via slicer selection! Check it out!
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Link to PBIX:
www.dropbox.com/s/w3wxd705aaf...
To enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Connect with me on Twitter!
/ powerbielite

[@karthikram5551]

Really appreciate for sharing your knowledge. The way you have explained was fantastic. You helped me decode some difficult task in a very easy way.

[@rauljimenez5485]

I love finding gems like this!! Thank you very much for such a simple, elegant solution!! This is amazing!

[@BIElite]

Haha thanks Raul! I was super pumped when I stumbled on this solution. Glad you got some use out of the video

[@reiffaulkner7879]

Parker! Long time no see. Great video. I'm impressed with your solution here given what PowerBI could do at that time. Keep up the good work.

[@kukieez]

Great tutorial! Is there any way to rename the Rows following the selected attribute in selected slicer? Thanks. Best Regards, SHE

[@Kentucky-ev7bd]

I have been trying to do this in excel for ages. Amaazing!!

[@BIElite]

Right! One of the best tricks on the channel.

[@MarceloSilva-ix9ks]

Man this blew my brain away! I will give it a go with a couple of million rows.

[@BIElite]

Hopefully it works out all right!

[@yaodegilde]

Hello, great solution. I've two questions actually. 1- Is it possible to use a calculated measure instead of quantity? 2- I want to keep my custom groups. Is it possible?

[@geertvanhove7663]

Great session. One question: is there also a way to define the levels within your hierarchy? In your case there are three levels, what if the user just wants twe levels (eg Year $ Product)

[@frankradewagen3488]

This is really awesome and unbelievable creative. Congratulations to Super PowerBI NINJA Master Grade 👍

[@BIElite]

Hahaha thanks Frank!

[@myazigi]

Again, another awesome trick from you Parker....thanks!!!

[@BIElite]

Anytime Mauricio! Thanks for watching

[@decentmendreams]

I would love to use this awesome technique but for some reason I am having a problem dragging the value fields into the value section of the Matrix . I have had the same problem before so what I did is I opened a brand new PBIX files to test this and it works perfectly but when I try to use this technique on my existing models I can't seem to do so-Could someone help me please?

[@ceresn]

hi! this is very helpful and i was able to replicate it in our reports. However, I have a question, can I do this and at the same time have dynamic columns (slicer for columns) from 1 source? I was trying to google but can find anything helpful for my inquiry. I hope to hear from you soon! Thanks!

[@mienzillaz]

very cool. question: if i have already hierarchy in place, let say lvl1 lvl2 lv3, and i want to have slicer to change all these levels at once to lvl1a lvl2a lvl3a.. how to achieve this?

[@idealizetube]

Hey, thanks for the tip. Could you please help me with one doubt? I did that, and everything works like it should. But, i tried one thig differently after that. I create one mesure (let's use the info of your video as an example) calculating the sum of Revenue for the last month. For that, i used the collum Revenue from the Sales table (not the pivot 1). But, when i tried to create a table with the mesure and the Value from the Pivot Table it gives me the same value for every line, when i was expecting the value from each one.

[@davidlewis7154]

Superb video - saved me a bunch of time this!

[@BIElite]

Great to hear, David!

[@teamofsteve]

This is really impressive, thanks for sharing

[@BIElite]

One of my favorite tricks!

[@manaralnuaimi4886]

very helpful, thanks alot

[@masgitz]

I can't import visuals in my company. Is there an alternative?

[@jan4826]

Thanks for this clever trick! Has anyone tried to add a slicer on top of this to filter specific values ​​based on the original table? For example, i like to see this dynamic matric on certain region, so i add a silcer with region from original table. However, the slicer is not working properly, the matrix reflect several different regions and data incorrect.

[@khardu]

OMG that's really awsome :O. Thanks Parker

[@BIElite]

Right?? I love this trick

[@vishakhakumar3854]

Thanks!! This is great

[@haydentam9531]

Very amazing 🤩

[@danielnichols3594]

Does this only work in Power BI or also in Power Pivot for Excel (2016)?

[@BIElite]

+Daniel Nichols I think you can use this in Power Pivot as long as you make the duplicate tables in PQ

[@raultorvisco2406]

Hi! If the data included on the table or matrix come from different tables, can I still use this method?... Or I should flat the table to get the data in only single one?...

[@BIElite]

+Raúl Torvisco Good question! Sadly I don’t have an answer for that. I’m not sure I would know how to make this work with related tables but please let me know if you give it a try!

[@BobSuijker]

@BI Elite any New insights to this? Exactly me question. My datamodels mostly contain fact tables and dim tables too. Say fact.sales , dim.item, dim.customer, dim.date. I’d like to use labels from the dim tables in such a model, would be awesome! Next to that I also wonder if it is possible to use facts from multiple tables in this model. Say sales facts and budget facts. This is a different thing i suppose. Any ideas? I Will ask around a bit more as well. Thanks in advance!

[@stefnielen6949]

awesome!

[@alejandrogarcia-mb4sv]

does it still work? i have this error: "You cant create a relationshop between these two columns because one of the columns mus thave unique values" : :(((

[@recruitementsolutions1588]

there could be a blank row or record in the table.

[@krishnakishorepeddisetti4387]

Great...a quick question....I can still see the eraser button on the top of the chiklet slicer ....i am aware that you have made the selection to "single selection". But if I click on the eraser on the top of slicer (if it is active) , will in create a mess in the table ?? Regards Kishore

[@BIElite]

Great question! I just clicked the eraser to see for myself and it doesn't actually do anything. Good to know! There's a link to the PBIX in the description if you want to try it out for yourself.

[@krishnakishorepeddisetti4387]

@@BIElite Thank you :)...your videos rock...keep up the good work. Best regards Kishore

[@truevaluebusinessintellige6146]

Have you tried this utilizing both rows and columns on a matrix?

[@BIElite]

Yep! I combined my trick for dynamic columns with this trick for dynamic hierarchies and it works well!

[@alexeybobin5686]

Awesome!

[@BIElite]

Thanks Alexey!

[@brikimbran]

Jesus! Where have you been? Subbed.

[@BIElite]

Haha this is one of my favorite tricks

[@Urgosandspirit]

Hey Parker Thanks for sharing first of all I am a bit concerned since this set up does not look that well from the model structure point Let me tell you what exactly I see dangerous or concerning: - all of the new "PIVOT" tables are factual - It is not good to have a relationship between factual tables or at least i cannot visualize how this can work on a regular star-schema data model - I wonder how scalable this method it as it feels referencing the data 2-3 times lowers the performance at least by same amount - looks like this method works as long as all of the needed fields to be used as table context are inside one table, but that once again at some point may contradict the denormalization Cheers

[@pbihari0214]

Can you add a measure by dividing quantity by revenue

[@BIElite]

Yes, you should be able to.

[@nboisen]

So cool!

[@BIElite]

+nboisen Thanks!!

[@nabjad94]

this actually is really cool new prospect on unpivoting. but isn't it horrible for anything larger than a small data set?

[@BIElite]

+jad freyha Surprisingly I was able to use this method on roughly 300Mb of data from txt files. So it doesn’t have to be too small :)

[@abhimalhotra3719]

Hi can you add a video where we can create a Pie of Pie chart using Python Script

[@BIElite]

Hey Abhi, can you elaborate on this? I'm unsure what you mean by Pie of Pie chart.

[@abhimalhotra3719]

@@BIElite there are two things in it. for example there are 5 main issues and out of these 5 issues ,1 one of the issues has sub category so if i want them to be showed in a another pie or bar (their count or percentage) . secondly so if there are 15 issues but they will not fit in one pie chart as it may look wired so is there any python code which will show 7 issues in main pie chart and other 8 issues will show in bar adjacent to pie , connecting to main pie , the chart which we create in excel Pie of Bar/pie of pie.

[@Ak-yq5cb]

Throws an error that one columns should have unique value .. while creating relation between Sales pivot 1 and sales pivot 2 @BI Elite

[@geert0202]

Hey Parker - what a neat trick I have a question though ;-) Is it only possible to do if you have one table? - what about standard relationships? I have some measures that depend on related tables - but if I relate tables to the pivoted table it all messes up ;-(

[@BIElite]

Hi Geert, I believe this only works with single tables... I can't even comprehend how you would get a series of tables to work with this unpivot method. Hopefully someone smarter than me can figure it out!

[@pratikfutane8131]

Super Intelligent!!

[@BIElite]

Thank you Pratik!

[@darryll127]

Well conceptually interesting this is not viable for working with measures.

[@denwo1982]

This would not work if you have different granularity of data

[@BIElite]

This is a very specific trick.

[@denwo1982]

BI Elite sorry not sure what you mean? I haven’t seen it can be done, I am coming from a Qlikview background trying to convert to Power BI, Qlik had this great function called Cyclic button, trying to do something similar in Power BI but unsuccessful

[@zibroyd]

Great! Thank you!

[@BIElite]

No problem! Thanks for watching