Thank you for the insights! Please, can you do an example of creating hierarchies with data other than dates or Georgraphy? Like Components and subcomponents of an organization: Business Unit A (drills down) ---> Business Development ---> Sales ---> Pre-sales... Thank you!!!
I have a hierarchy of labels that were created by analysis the qualitative data... essentially pulling themes out of the qualitative data as a series of observations. I map those themes into a hierarchy to view sort and analyse the qualitative information. I have 2 ways of doing this (not saying I'm right, I'm actually wondering how to apply this to my scenario)... 1 create a single table that maps thr hierarchy using the unique IDs of each record that is mapped within the hierarchy. 2 I create a series of tables into a hierarchy of tables, where each connection between a record in level 1 and level 2, is captured by linking the 2 IDs together that map level 1 records to level 2 records.... same for level 2 records mapping to level 3 records. And so on... so in the data model, it is the tables that are creating the hierarchy. I'm wondering whether this feature will allow me to improve this approach... if it's even an approach at all... 1
You guys are awesome.. been following ya'll for over a year... I have been trying to find someone to tell me why my primary date field lost its Hierarchy... you nailed it ! Now it makes sense if it's related to a Calendar Table it goes away... THANK YOU .. I have been struggling for months on this. Thank you!
@@GuyInACube Now I just need to find your tutorial on how to share with my team a table I created from SAP so they don't have to recreate the wheel... is that a dataflow or dataset I need to share?
Geography is a good one to work with also. I picked up on a helpful technique to sync latitude/longitude-plotted data points, with a zoomable view of Bing Maps to country, state, city, and zip including rollups of lat/long data at each of the geo hierarchy tiers. Unfortunately I couldn't get a filter button to operate the hierarchy, so I created bookmarks for the user to access saved views into the country, state, city and lat/long levels. It worked out and saved time, but the question over how to filter through hierarchy tiers is something I'm still working on. Suggestions?
I love the new desktop wallpaper! And it was a great example at the end about using pre-defined hierarchies to ensure data integrity. Great video, as always!
Great Video Patrick!! I am looking something similar to the example which you showed up in the starting of the video, which is "Organizational Hierarchy". The thing is I want to create an operations dashboard in which I need to show let's say attrition rate of some manager. And the same manager might have some different supervisor and if I want to see attrition rate of that supervisor, then all attritions should get aggregated to that supervisor. Can we do this in Power BI?
Thanks so much for explaining the completely unintuitive logic at work here. I created about 2 dozen relationships in one go (not realizing one of them had decided to filter in both directions by default), and when I got back to my visuals tab I was mystified as to why my calendar table's date hierarchy had disappeared and refused to come back.
Hi , I have a question. How do you use the calendar table when there are multiple dates in a table? Ie: I have 2 dates in a table created date and end date. In my calendar table, I have short month as an option. I want to show both dates with the short month.
Thank you so much for the informative video..I have one question please, I have a chart showing percentages of what people from different areas(cities)prefer to do in weekends, let's say 23% prefer to read books , but this percentage covers the whole country (different cities), how to show from that chart where are people who prefer to read with percentage/city? Thanks in advance
Nice video :) Can we have the drill down hierarchy for categorical fields as well? Like we could create a date hierarchy and are able to drill to year,quarter,month,date . Similarly can I drill down for may be Product category, Product subcategory, Product Name etc in the same view.(As an example)
Hey Patrick, I found this video for "hierarchy in power bi using different table" . You have mentioned that we need to create a relationship in order for the hierarchy to work which I understood but "Expand next level" drill shows exactly the things which we want (note: "switch next level" doesn't work) so in that way its correct right
I have created a custom hierarchy includes category-question - quarter+year (get it as text from source). The drill down works only till question and not for quarter. Also is there a way to show multiple columns on the axis? Like I have category (which can be drilled down to question) and quarter on the y axis?
Is there any hierarchical table, eg if I select the group head name the field of table name should be replaced with FH name and if I select FH name it should be replaced with CH name but the name should be replaced in same table
Hi Patrick, what do I do if I have several dates in a table, like creation_date, start_date, end_date and so on? I can not crate multiple one to many relationships between a field in one table and many different fields in another. Is there a way to do this?
Is there an article that explains in depth how hierarchies work, and what recalculations they trigger? I inherited a report with a 7 level Product hierarchy and the further I drill down, the more it slows. How do I fix something like that?
Dear Patrick, assume I have defined two hierarchies, how can the end user switch from one to the other? End user is the user of the published report, not the report creator
Today, they can't easily switch it. Maybe you could get fancy with a measure and if statements, and then switch with a slicer. This feature is really what you are looking for though: docs.microsoft.com/power-platform-release-plan/2020wave1/business-intelligence/visual-personalization-end-users
Is there a way to add the hierarchy to a field that it seems to be missing from? I don't want to use the date table but I have a filed which is a "date" data type with no hierarchy and cant get it back...
How can we show non aggregated values in matrix visualization Hierarchies. Eg: I need to display name, address etc... of Individuals under different HOD and at HOD level I would like to display the details corresponding to HOD.
Q. Patrick, what if I want to display my hierarchy in a columns section of matrix. For example I have org hierarchy slicer and I want to display the selected org level in the visual. I notice that powerbi is not displaying the selected value in hierarchy, it always displays the first level value in hierarchy.
Thanks Patrick as always, for great content. I have a question. I just created a hierarchy, and I can't re-arrange it after I have created it (meaning drag the fields into descending order of Year, Month, Date). I could have SWORN we used to be able to do this. Then, at the very end, you mentioned that you should drag the fields in to the hierarchy IN THE ORDER they should appear. Did something change in the capabilities of hierarchies? Did I hallucinate the ability to re-arrange them? I am pretty sure I didn't but happy to be corrected. Hope to see you and Adam at the Power Platform Summit next month!
Hello all. Is it possible to for one variable(same column) to show two dimensions in the same row? I need to show by day the Count and the $ Amount of the variable Invoices. I know how to do this in two different columns but not on the same column. Thank you for your help.
Hi, I would like to expand all fields at once when I click in another chart. I can make the example: - on the left side I have a donut chart with the continents - on the right side I have the table with the categories (continents) and if I click on the + signal it's possible to make the drill down and see the details for each country inside of the respective continent But I would like to click on the continent on the donut visual (on the left) and expand to the second level of the hierarchy on the table visual (on the right). Is it possible to make that? Thank you!
hi is there any way i can apply some erlang forumals used for call center agents like agents forumlas to get required agents and sevice level formula to get expected service level!???
Thanks Patrick. Quick question...do you have the ability to display the drill hierarchy so the end user knows what hierarchy data fields are in the drill without having to actually drill into the report? Can you display it via a tool tip?
Hi I am doing a survey analysis and I am required to add a drill down hierarchy as a slicer. And there are a lot of multiple response questions as well as a lot of other slicers (demographic questions which I have used as look up tables). Now these multiple response questions are not getting updated for the hierarchy slicers but are showing correct results for other slicers. Any suggestions on how to add a lookup table for the hierarchy fields (eg country > state > city > Town)
Your videos are super helpful Patrick, This helped me create a hierarchy for Supervisor > Employee...but I'd also like to bring a time element into it, "Rep A reported to Manager A from Jan - March, but then reported to Manager B from April through today" -- is there a way to more easily visualize this time based hierarchy? Thanks!
Hi Patrick, scenio like: 1) dashboard should display by default current month ( if current month data not updated in source, it should be latest month in source), and also slicer should update dynamically switch from one month to another month depending on the current or latest month values. 2) is it possible to set conditional formatting for slicer or chicklet slicer?
There is a filter for dates (for a visual, for the page or the entire model). In the filter type box, select "Relative Date Filtering" (vs basic or advanced), then in the last drop down you can CALENDAR Weeks, Months or Years - and you can select how many in the "last number of ....". By selecting Calendar Month, my data through today (Aug 14, 2019) is filtered through July 31, 2019. If I picked Calendar Year the data is filtered through Dec 31, 2018. I just ran across this in the past couple of days. I have not tried it with different data sets yet, so i don't know how it acts if I only have data through July 20, 2019 . If you pick the type of "just month" or "year" the filter is through today.
Hey Patrick, My requirement is in table visual have month with some measures like cliks impressions along with I need week drill down in table visual Can u plz let me know on this.
Hi Patrick, Great video! As always there is a lot to learn from you and from Adam! I have a but. It's probably easy but I can not find a way to changes the order that I have in the first place! For instance, I started with a Category / Sub Category / Segment, but then I would like to have Category / Segment / Sub Category. I just want to move the Segment up above the Sub Category. I could do a new hierarchy - But then I would have to change it everywhere. Is there a way where I can change the order, or is there a way where I can remove the one that is already in the hierarchy? Best regards Kent
Can hierarchies include measures from multiple tables in the same hierarchy. For example, I have a people in the company, rolling up to area of the company, rolling up to line of business (LOB). These are spread across different tables.
while using direct query, i can't see this date hierarchy. can you please share, how to create hierarchy while using direct query instead of tables from datasets.
Thanks Patrick, I have one question, The default Date Hierarchy or any hierarchy will not work in the Direct Query, In such cases I think we can not create any hierarchy for date(day/month/year). Is only option we have is to create Calculated column?? OR any other way we can build hierarchy for the date in direct query.
Hi Patrick, thanks again for the video! However, there's one thing which is itching me too much in Power BI. In my organisation we want to show sales organisation fact data based on different sales hierarchies which are time-dependent. E.g. Sales hierarchy 2020 vs Sales hierarchy 2021 and how sales values differ between calendar years. As I was told, Power BI / SSAS does not support time-dependent parent-child hierarchies, how would this be realized then in SSAS / Power BI?
Hi Patrick. Thanks for a great video! I am new to PowerBI, and I am looking to get some data from SAP BW into PBI to create dashboards, and we are having a problem of "flattening" of hierarchy when getting data from BW into PBI. Do you know if we can create master data tables, with parent child hierarchy relationships and connect to the flattened dimensions to read hierarchy info into our reports? Your inputs would be greatly appreciated! Thanks!
Hi Patrick! Is there an easy way to assign hierarchies if the date info do not follow a calendar year? For example, if a FISCAL YEAR started in September versus a Calendar year that starts in January? In that scenario, your first calendar of the fiscal year would be Sept-Nov rather than Jan-Mar. Love the vids! Thank you!
You may have two different hierarchies defined in your calendar table. One for Calendar Year, and another for Fiscal Year. The fiscal year hierarchy would have the Fiscal Year/Fiscal Quarter/Fiscal Month and so on.
Hi Patrick: love your videos. Can you get one together on the "Hierarchical Slicer"? This [as you know is a preview feature] in the latest release and must be selected from within the "options" after which you have to restart Power BI. But afterwards, the actual Hierarchical Slicer visual does not show up on my desktop. Interestingly, if I just use the regular slicer, I can select and build a hierarchy but not sure if this might lead to problems. Because the new slicer is included as an option in the latest release, I would not think that I would have to download it from the App Store just to get the visual in my visual selections area. Any help would be much appreciated.
Hi Patrick, could you do a video about natural vs. unnatural hierarchies? I get the difference and understand that natural hierarchies have better performance, but I don't find them easy to use because it always shows double information...
I love the feature and always enjoy your videos. However, my question is the same for many Power BI features: How can I automate this? Imagine you build out a template to be used within many organizations, structured similarly. But each has a unique depth. I'd love it if I could just tell Power BI something like IF depth = # add that number of columns the to hierarchy.
Hi Patrick With regards to Hierarchies and Live connections from SSAS (tabular, in my case) do you know of any resource that can help with this? Power BI (appears) to import all dates as strings and therefore my pre-created hierarchies does not work for forecasting and trend analysis. Thanks Danny
From an accounting perspective, lets say I have 1000 accounts (level 0). These accounts roll into 500 summary accounts (level 1). These roll into 100 summary accounts (level 2). These roll into 20 summary accounts (level 3). these roll into 4 summary accounts (level 4)....etc.... How would you go about something like this? The only thing I can think of is to have each level be it's own table and join level 0 to level 1, level 1 to level 2, etc....
Hi Patrick: Great video you have done here. Congratulations! Would you please make a video (o send me a link where I can get it) explaining how to create a Calendar table in Power Bi. It is very useful to have a fixed Calendar table to be used in our models. I will be waiting for your answer. Best!
Hector Alvaro Rojas check sqlbi power bi calendar on google. There are other pbi expert blogs with their own dax codes if you search power bi calendar.
Hi Thank you for sharing great videos. I need help in creating multi-column relationship. I have a survey of 20 questions. Each question is placed in a column and each question has score rages from 1-5. These questions are in table 1. I created table 2 to translate the score to 1- strongly agree; 2 agree; 3 neutral; 2 disagree; and 5 strongly disagree. In creating the relationship between two tables, power bi doesn’t allow me to link table two to more than one column. How can I please help.
What is it dosent make a Hierarchy? I have date in database but in powerbi it just become a column and if i try convert it into a Hierarchy it just make a new column under it called the same. so no year,month,day every row is like 2022-01-01
I am a big fan of the hierarchies. However, when including attributes in a hierarchy in Power BI Premium, they are not shown when connecting to the model via Excel. Did anyone else experience the same problem? Is there a solution to that problem?
I've linked some tables together but i'm having a bit of trouble, if we use the example of Table A and Table B, Table A displays all projects, Table B displays resource for the project. The report i'm trying to make has fields from both Table A and B, however when i display Table A columns after Table B columns, where the table B columns are blank the subsequent values are too!!! even though i know they have values, when i move the column ordering so that the columns are ahead of table B columns they suddenly populate. Does anyone know how i could get around this?
Actually i found one trick that when the date hierachy disappeared for some reason, just delete the column or measure and create the same thing and Power BI will automatically re-create the hierachy :)
HI Patrick, this may not be the best video to ask this question but .... I'm going to do it anyway... I have a date table and a time table each joined to my Fact on the respective Date and Time keys. I setup a graph with Date --> HourMinString hierarchy for example. Is it possible for me to control the way the hierarchy is ordered when I expand all to the lowest level (in this case HourMinString)? Currently, I get something like this when I do this... 01/06/2020 00:00 02/06/2020 00:00 03/06/2020 00:00 ............. 01/06/2020 01:00 02/06/2020 01:00 etc I am trying to achieve.... 01/06/2020 00:00 01/06/2020 01:00 01/06/2020 02:00 etc I could be missing something conceptually but just thought I'd ask. Thanks
@@GuyInACube Apparently I'm not understanding this. No matter what I do I can't get the time to be part of a hierarchy. It just doesn't give the option to add it to the hierarchy.
Don't really understand what is so special about those hierarchies. If you have a few tables that have hierarchy relationship like Category-Subcategory-SubSubcategory, you do not need to explicitly define them as hierarchy. You can just drill down/up without any hierarchy definition.
Yup, that's true. Although giving hints to the engine and possibly reducing the number of clicks is helpful. Also, most tables I see from customers have really horrible naming schemes. Having a pre-defined hierarchies can help reduce confusion, or even highlight items that are related that you may not have been aware of.
Some of it is personal preference. They do potentially add ease for the report author though. If i select Month, within a hierarchy, it will select everything in the hierarchy automatically to make sure i don't forget something. Otherwise, i have to add them manually one by one.
Hi Patrick, Great video for date hierarchy but what if you want to create a numbered list hierarchy. Column 1 1.1 1.1.1 1.1.2 1.1.2.1 1.2 1.2.1 1.2.3.1 etc? I have created a numbered list hierarchy but when i select 1, everything associated with 1.1 ( 1.1 , 1.1.1, 1.1.2, 1.1.2.1) is also selected. Path 1 = if( PATHITEM('Project'[WBS - Copy],1,TEXT) = "1", PATHITEM('Project'[WBS - Copy],1,TEXT), BLANK()) Path 2 = VAR PathLen = PATHLENGTH ( 'Project'[WBS - Copy] ) VAR Delimiter = "." RETURN IF ( PathLen >= 2, 'Project'[Path 1] & Delimiter & PATHITEM ( 'Project'[WBS - Copy], 2, TEXT ), BLANK() ) Path 3 = VAR PathLen = PATHLENGTH ( 'Project'[WBS - Copy] ) VAR Delimiter = "." RETURN IF ( PathLen >= 3, 'Project'[Path 2] & Delimiter & PATHITEM ( 'Project'[WBS - Copy], 3, TEXT )
) Path 4 = VAR PathLen = PATHLENGTH ( 'Project'[WBS - Copy] ) VAR Delimiter = "." RETURN IF ( PathLen >= 4, 'Project'[Path 3] & Delimiter & PATHITEM ( 'Project'[WBS - Copy], 4, TEXT ) ) Path 5 = VAR PathLen = PATHLENGTH('Project'[WBS - Copy]) VAR Delimiter = "." RETURN IF(PathLen >= 5, 'Project'[Path 3]&Delimiter&PATHITEM('Project'[WBS - Copy],5,TEXT), BLANK())
Hi Patrick: By the way, I click on your link get the sample (guyinacu.be/hierarchysample ) and I got some error message. Would you mind solving this situation so we can download the sample? Regards!
Guy in a cube rocks. I worked with Tableau in the past and now I have to adapt to Power BI and thanks to you I can learn it pretty fast.
Power BI is just spectacular. The guy who thought that out in Microsoft deserves a medal and a big bag of money!
Your videos are always right on point. Entertaining, easy to follow and informative - especially for a new Power BI user like me. Thank you so much.
explaining the table relationship and why the heirarchy disappears is gold. thanks!
Woot! Glad this was useful.
Thank you for the insights! Please, can you do an example of creating hierarchies with data other than dates or Georgraphy? Like Components and subcomponents of an organization: Business Unit A (drills down) ---> Business Development ---> Sales ---> Pre-sales... Thank you!!!
I have a hierarchy of labels that were created by analysis the qualitative data... essentially pulling themes out of the qualitative data as a series of observations. I map those themes into a hierarchy to view sort and analyse the qualitative information.
I have 2 ways of doing this (not saying I'm right, I'm actually wondering how to apply this to my scenario)...
1 create a single table that maps thr hierarchy using the unique IDs of each record that is mapped within the hierarchy.
2 I create a series of tables into a hierarchy of tables, where each connection between a record in level 1 and level 2, is captured by linking the 2 IDs together that map level 1 records to level 2 records.... same for level 2 records mapping to level 3 records. And so on... so in the data model, it is the tables that are creating the hierarchy.
I'm wondering whether this feature will allow me to improve this approach... if it's even an approach at all...
1
You guys are awesome.. been following ya'll for over a year... I have been trying to find someone to tell me why my primary date field lost its Hierarchy... you nailed it ! Now it makes sense if it's related to a Calendar Table it goes away... THANK YOU .. I have been struggling for months on this. Thank you!
Woot! Great to hear 👊 And, thank you for watching us over the last year!
@@GuyInACube Now I just need to find your tutorial on how to share with my team a table I created from SAP so they don't have to recreate the wheel... is that a dataflow or dataset I need to share?
I love your energy Patrick! Thanks to you and Adam for all your informational videos.
This is exactly what I needed. I'm using it to create an academic year and quarter structure. Thanks so much!
Excellent! Love that it helps you. Thanks for watching 👊
Geography is a good one to work with also. I picked up on a helpful technique to sync latitude/longitude-plotted data points, with a zoomable view of Bing Maps to country, state, city, and zip including rollups of lat/long data at each of the geo hierarchy tiers. Unfortunately I couldn't get a filter button to operate the hierarchy, so I created bookmarks for the user to access saved views into the country, state, city and lat/long levels. It worked out and saved time, but the question over how to filter through hierarchy tiers is something I'm still working on. Suggestions?
literally exactly what I needed and to the point THANK YOU
Thank you..i was stuck on this for 30 mins
I love the new desktop wallpaper! And it was a great example at the end about using pre-defined hierarchies to ensure data integrity. Great video, as always!
Great Video Patrick!!
I am looking something similar to the example which you showed up in the starting of the video, which is "Organizational Hierarchy". The thing is I want to create an operations dashboard in which I need to show let's say attrition rate of some manager. And the same manager might have some different supervisor and if I want to see attrition rate of that supervisor, then all attritions should get aggregated to that supervisor. Can we do this in Power BI?
Awesome video bro
Hey Patrick. Another great video. Any idea how I can have a hierarchy with a dynamic depth of items please?
Great energy and good content
This is what I need exactly in my current task. Thank you.
Love it! Glad it helped you out. 👊
Thanks so much for explaining the completely unintuitive logic at work here. I created about 2 dozen relationships in one go (not realizing one of them had decided to filter in both directions by default), and when I got back to my visuals tab I was mystified as to why my calendar table's date hierarchy had disappeared and refused to come back.
Hi , I have a question. How do you use the calendar table when there are multiple dates in a table? Ie: I have 2 dates in a table created date and end date. In my calendar table, I have short month as an option. I want to show both dates with the short month.
Thank you so much for the informative video..I have one question please, I have a chart showing percentages of what people from different areas(cities)prefer to do in weekends, let's say 23% prefer to read books , but this percentage covers the whole country (different cities), how to show from that chart where are people who prefer to read with percentage/city?
Thanks in advance
Nice video :)
Can we have the drill down hierarchy for categorical fields as well? Like we could create a date hierarchy and are able to drill to year,quarter,month,date . Similarly can I drill down for may be Product category, Product subcategory, Product Name etc in the same view.(As an example)
How do you get a comparison year for your data set in a line chart when the year starts in JUne?
Thank you, very clear, good pace.
Hey Patrick, I found this video for "hierarchy in power bi using different table" . You have mentioned that we need to create a relationship in order for the hierarchy to work which I understood but "Expand next level" drill shows exactly the things which we want (note: "switch next level" doesn't work) so in that way its correct right
I have created a custom hierarchy includes category-question - quarter+year (get it as text from source).
The drill down works only till question and not for quarter.
Also is there a way to show multiple columns on the axis? Like I have category (which can be drilled down to question) and quarter on the y axis?
nice tutorial
I want to ask, what is the difference between using DATESINPERIOD and SAMEPERIODLASTYEAR syntax?
Is there any hierarchical table, eg if I select the group head name the field of table name should be replaced with FH name and if I select FH name it should be replaced with CH name but the name should be replaced in same table
Hi Patrick, what do I do if I have several dates in a table, like creation_date, start_date, end_date and so on? I can not crate multiple one to many relationships between a field in one table and many different fields in another. Is there a way to do this?
I am also having this same issue and the automatic hierarchies are not showing for all the dates I have.
Is there an article that explains in depth how hierarchies work, and what recalculations they trigger? I inherited a report with a 7 level Product hierarchy and the further I drill down, the more it slows. How do I fix something like that?
Thanks that was helpful.
Dear Patrick, assume I have defined two hierarchies, how can the end user switch from one to the other? End user is the user of the published report, not the report creator
Today, they can't easily switch it. Maybe you could get fancy with a measure and if statements, and then switch with a slicer. This feature is really what you are looking for though: docs.microsoft.com/power-platform-release-plan/2020wave1/business-intelligence/visual-personalization-end-users
Hello, can we add a location hierarchy to shape map on power bi?
Is there a way to add the hierarchy to a field that it seems to be missing from? I don't want to use the date table but I have a filed which is a "date" data type with no hierarchy and cant get it back...
How can we show non aggregated values in matrix visualization Hierarchies. Eg: I need to display name, address etc... of Individuals under different HOD and at HOD level I would like to display the details corresponding to HOD.
Q. Patrick, what if I want to display my hierarchy in a columns section of matrix. For example I have org hierarchy slicer and I want to display the selected org level in the visual. I notice that powerbi is not displaying the selected value in hierarchy, it always displays the first level value in hierarchy.
Please add one playlist for how to develop custom visuals for searching a specific text for comma or space separated manner
thank youuuuu!!!!!! This video was so helpful!!!!
Thanks Patrick as always, for great content. I have a question. I just created a hierarchy, and I can't re-arrange it after I have created it (meaning drag the fields into descending order of Year, Month, Date). I could have SWORN we used to be able to do this. Then, at the very end, you mentioned that you should drag the fields in to the hierarchy IN THE ORDER they should appear. Did something change in the capabilities of hierarchies? Did I hallucinate the ability to re-arrange them? I am pretty sure I didn't but happy to be corrected. Hope to see you and Adam at the Power Platform Summit next month!
Hello all. Is it possible to for one variable(same column) to show two dimensions in the same row? I need to show by day the Count and the $ Amount of the variable Invoices. I know how to do this in two different columns but not on the same column. Thank you for your help.
Hi,
I would like to expand all fields at once when I click in another chart.
I can make the example:
- on the left side I have a donut chart with the continents
- on the right side I have the table with the categories (continents) and if I click on the + signal it's possible to make the drill down and see the details for each country inside of the respective continent
But I would like to click on the continent on the donut visual (on the left) and expand to the second level of the hierarchy on the table visual (on the right). Is it possible to make that? Thank you!
hi
is there any way i can apply some erlang forumals used for call center agents
like agents forumlas to get required agents
and sevice level formula to get expected service level!???
Thanks Patrick. Quick question...do you have the ability to display the drill hierarchy so the end user knows what hierarchy data fields are in the drill without having to actually drill into the report? Can you display it via a tool tip?
Hi I am doing a survey analysis and I am required to add a drill down hierarchy as a slicer. And there are a lot of multiple response questions as well as a lot of other slicers (demographic questions which I have used as look up tables). Now these multiple response questions are not getting updated for the hierarchy slicers but are showing correct results for other slicers. Any suggestions on how to add a lookup table for the hierarchy fields (eg country > state > city > Town)
Hi Can you make me understand after building a report how to Validate the report. Thanks for these awesome informative videos.
can we have date hierarchy in direct query mode
Your videos are super helpful Patrick, This helped me create a hierarchy for Supervisor > Employee...but I'd also like to bring a time element into it, "Rep A reported to Manager A from Jan - March, but then reported to Manager B from April through today" -- is there a way to more easily visualize this time based hierarchy? Thanks!
Hi Patrick, scenio like: 1) dashboard should display by default current month ( if current month data not updated in source, it should be latest month in source), and also slicer should update dynamically switch from one month to another month depending on the current or latest month values.
2) is it possible to set conditional formatting for slicer or chicklet slicer?
On dashboard, you can always see Last refresh date.
There is a filter for dates (for a visual, for the page or the entire model). In the filter type box, select "Relative Date Filtering" (vs basic or advanced), then in the last drop down you can CALENDAR Weeks, Months or Years - and you can select how many in the "last number of ....". By selecting Calendar Month, my data through today (Aug 14, 2019) is filtered through July 31, 2019. If I picked Calendar Year the data is filtered through Dec 31, 2018. I just ran across this in the past couple of days. I have not tried it with different data sets yet, so i don't know how it acts if I only have data through July 20, 2019 .
If you pick the type of "just month" or "year" the filter is through today.
Hey Patrick,
My requirement is in table visual have month with some measures like cliks impressions along with I need week drill down in table visual
Can u plz let me know on this.
Hi Patrick,
Great video! As always there is a lot to learn from you and from Adam!
I have a but. It's probably easy but I can not find a way to changes the order that I have in the first place! For instance, I started with a Category / Sub Category / Segment, but then I would like to have Category / Segment / Sub Category. I just want to move the Segment up above the Sub Category.
I could do a new hierarchy - But then I would have to change it everywhere.
Is there a way where I can change the order, or is there a way where I can remove the one that is already in the hierarchy?
Best regards Kent
Can hierarchies include measures from multiple tables in the same hierarchy. For example, I have a people in the company, rolling up to area of the company, rolling up to line of business (LOB). These are spread across different tables.
How can you use hierarchies across multiple tables on a shared dataset? Is that even possible?
Hi I Have a proble to draging feilds to hierarchy at the 4:14 time line
can i change any settings
while using direct query, i can't see this date hierarchy. can you please share, how to create hierarchy while using direct query instead of tables from datasets.
Sir Video is superb 👌, how can we show Qtr, month , year in same chart without drill down, in one view i can see all like in Excel , pls assist.
Thanks Patrick, I have one question, The default Date Hierarchy or any hierarchy will not work in the Direct Query, In such cases I think we can not create any hierarchy for date(day/month/year). Is only option we have is to create Calculated column?? OR any other way we can build hierarchy for the date in direct query.
Thank you 👍
Is parallel drill down posible in power bi
Awesome! i've been looking for a way to change the Day (1,2,3) to the Date. Thanks:)
Greate video, as usual!!
Thanks Kenneth! 👊
Hi Patrick, thanks again for the video! However, there's one thing which is itching me too much in Power BI. In my organisation we want to show sales organisation fact data based on different sales hierarchies which are time-dependent. E.g. Sales hierarchy 2020 vs Sales hierarchy 2021 and how sales values differ between calendar years.
As I was told, Power BI / SSAS does not support time-dependent parent-child hierarchies, how would this be realized then in SSAS / Power BI?
Thak you for the video.. very helpfull.
Hi Patrick. Thanks for a great video! I am new to PowerBI, and I am looking to get some data from SAP BW into PBI to create dashboards, and we are having a problem of "flattening" of hierarchy when getting data from BW into PBI.
Do you know if we can create master data tables, with parent child hierarchy relationships and connect to the flattened dimensions to read hierarchy info into our reports?
Your inputs would be greatly appreciated! Thanks!
Hi Patrick! Is there an easy way to assign hierarchies if the date info do not follow a calendar year? For example, if a FISCAL YEAR started in September versus a Calendar year that starts in January? In that scenario, your first calendar of the fiscal year would be Sept-Nov rather than Jan-Mar. Love the vids! Thank you!
You may have two different hierarchies defined in your calendar table. One for Calendar Year, and another for Fiscal Year. The fiscal year hierarchy would have the Fiscal Year/Fiscal Quarter/Fiscal Month and so on.
Hi Patrick: love your videos. Can you get one together on the "Hierarchical Slicer"? This [as you know is a preview feature] in the latest release and must be selected from within the "options" after which you have to restart Power BI. But afterwards, the actual Hierarchical Slicer visual does not show up on my desktop. Interestingly, if I just use the regular slicer, I can select and build a hierarchy but not sure if this might lead to problems. Because the new slicer is included as an option in the latest release, I would not think that I would have to download it from the App Store just to get the visual in my visual selections area. Any help would be much appreciated.
Hi Patrick, could you do a video about natural vs. unnatural hierarchies? I get the difference and understand that natural hierarchies have better performance, but I don't find them easy to use because it always shows double information...
I love the feature and always enjoy your videos. However, my question is the same for many Power BI features: How can I automate this? Imagine you build out a template to be used within many organizations, structured similarly. But each has a unique depth. I'd love it if I could just tell Power BI something like IF depth = # add that number of columns the to hierarchy.
Hi Patrick
With regards to Hierarchies and Live connections from SSAS (tabular, in my case) do you know of any resource that can help with this? Power BI (appears) to import all dates as strings and therefore my pre-created hierarchies does not work for forecasting and trend analysis.
Thanks
Danny
From an accounting perspective, lets say I have 1000 accounts (level 0). These accounts roll into 500 summary accounts (level 1). These roll into 100 summary accounts (level 2). These roll into 20 summary accounts (level 3). these roll into 4 summary accounts (level 4)....etc....
How would you go about something like this? The only thing I can think of is to have each level be it's own table and join level 0 to level 1, level 1 to level 2, etc....
Hi Patrick:
Great video you have done here. Congratulations!
Would you please make a video (o send me a link where I can get it) explaining how to create a Calendar table in Power Bi.
It is very useful to have a fixed Calendar table to be used in our models.
I will be waiting for your answer.
Best!
Hector Alvaro Rojas check sqlbi power bi calendar on google. There are other pbi expert blogs with their own dax codes if you search power bi calendar.
Does this help: th-cam.com/video/gyvhM5eiT0U/w-d-xo.html
@@GuyInACube This is what I was looking for!
Thanks for sharing it!
And that about time hierarchy? How i do it? Have you any link?
Hi
Thank you for sharing great videos.
I need help in creating multi-column relationship.
I have a survey of 20 questions. Each question is placed in a column and each question has score rages from 1-5. These questions are in table 1.
I created table 2 to translate the score to 1- strongly agree; 2 agree; 3 neutral; 2 disagree; and 5 strongly disagree.
In creating the relationship between two tables, power bi doesn’t allow me to link table two to more than one column.
How can I please help.
What is it dosent make a Hierarchy?
I have date in database but in powerbi it just become a column and if i try convert it into a Hierarchy it just make a new column under it called the same. so no year,month,day
every row is like 2022-01-01
Ive seen some have like the Dates and UNDER the Dates there are Weeks then Months on the X-Axis?
Anybody knows how to do that?
I am a big fan of the hierarchies. However, when including attributes in a hierarchy in Power BI Premium, they are not shown when connecting to the model via Excel. Did anyone else experience the same problem? Is there a solution to that problem?
i love it!
I've linked some tables together but i'm having a bit of trouble, if we use the example of Table A and Table B, Table A displays all projects, Table B displays resource for the project. The report i'm trying to make has fields from both Table A and B, however when i display Table A columns after Table B columns, where the table B columns are blank the subsequent values are too!!! even though i know they have values, when i move the column ordering so that the columns are ahead of table B columns they suddenly populate. Does anyone know how i could get around this?
Actually i found one trick that when the date hierachy disappeared for some reason, just delete the column or measure and create the same thing and Power BI will automatically re-create the hierachy :)
Great Stuff
HI Patrick, this may not be the best video to ask this question but .... I'm going to do it anyway...
I have a date table and a time table each joined to my Fact on the respective Date and Time keys. I setup a graph with Date --> HourMinString hierarchy for example. Is it possible for me to control the way the hierarchy is ordered when I expand all to the lowest level (in this case HourMinString)?
Currently, I get something like this when I do this...
01/06/2020 00:00
02/06/2020 00:00
03/06/2020 00:00
.............
01/06/2020 01:00
02/06/2020 01:00
etc
I am trying to achieve....
01/06/2020 00:00
01/06/2020 01:00
01/06/2020 02:00
etc
I could be missing something conceptually but just thought I'd ask.
Thanks
How would I add Hour and Minute to the Hierarchy?
You can create those hours and minutes column while creating your custom calendar date table using calendar () and can use it for hierarchy
You would need fields in your model for those values. Either Time, or two columns - one for hours and one for minutes.
@@GuyInACube Apparently I'm not understanding this. No matter what I do I can't get the time to be part of a hierarchy. It just doesn't give the option to add it to the hierarchy.
Don't really understand what is so special about those hierarchies. If you have a few tables that have hierarchy relationship like Category-Subcategory-SubSubcategory, you do not need to explicitly define them as hierarchy. You can just drill down/up without any hierarchy definition.
Yup, that's true. Although giving hints to the engine and possibly reducing the number of clicks is helpful. Also, most tables I see from customers have really horrible naming schemes. Having a pre-defined hierarchies can help reduce confusion, or even highlight items that are related that you may not have been aware of.
@@GuyInACube Good point !
I don't see any value from having hierarchies. What's wrong with year, month and day on axis instead of hierarchy?
Some of it is personal preference. They do potentially add ease for the report author though. If i select Month, within a hierarchy, it will select everything in the hierarchy automatically to make sure i don't forget something. Otherwise, i have to add them manually one by one.
im already annoyed, i already did an export, uploaded the data and its doing nothing.... like nothing at all
Hi Patrick,
Great video for date hierarchy but what if you want to create a numbered list hierarchy.
Column
1
1.1
1.1.1
1.1.2
1.1.2.1
1.2
1.2.1
1.2.3.1
etc?
I have created a numbered list hierarchy but when i select 1, everything associated with 1.1 ( 1.1
, 1.1.1, 1.1.2, 1.1.2.1) is also selected.
Path 1 = if( PATHITEM('Project'[WBS - Copy],1,TEXT) = "1",
PATHITEM('Project'[WBS - Copy],1,TEXT), BLANK())
Path 2 =
VAR PathLen =
PATHLENGTH ( 'Project'[WBS - Copy] )
VAR Delimiter = "."
RETURN
IF (
PathLen >= 2,
'Project'[Path 1] & Delimiter
& PATHITEM ( 'Project'[WBS - Copy], 2, TEXT ), BLANK()
)
Path 3 =
VAR PathLen =
PATHLENGTH ( 'Project'[WBS - Copy] )
VAR Delimiter = "."
RETURN
IF (
PathLen >= 3,
'Project'[Path 2] & Delimiter
& PATHITEM ( 'Project'[WBS - Copy], 3, TEXT )
)
Path 4 =
VAR PathLen =
PATHLENGTH ( 'Project'[WBS - Copy] )
VAR Delimiter = "."
RETURN
IF (
PathLen >= 4,
'Project'[Path 3] & Delimiter
& PATHITEM ( 'Project'[WBS - Copy], 4, TEXT )
)
Path 5 =
VAR PathLen = PATHLENGTH('Project'[WBS - Copy])
VAR Delimiter = "."
RETURN
IF(PathLen >= 5,
'Project'[Path 3]&Delimiter&PATHITEM('Project'[WBS - Copy],5,TEXT), BLANK())
Can you put dates into buckets?
Hi, for me the link with Sample Download is not working ... " This is a 404 error, ... "
Thanks for calling that out. Sorry I missed it. It is corrected and working now.
Hi Patrick:
By the way, I click on your link get the sample (guyinacu.be/hierarchysample ) and I got some error message. Would you mind solving this situation so we can download the sample?
Regards!
Thanks for calling it out. It is corrected now.
too fast pls ^^
Kindly speak clear. Your accent is not clear and you are too fast.