When is a mouse not a mouse?

This is the first in the series of tools and technologies that I use to deal with the loss of functionality in my hands and arms. Check out this article for the lead up to this series.

Setting the stage

The issue I’m dealing with involves muscle atrophy in my hands and my arms. As a result, I’ve lost a lot of strength in my hands and arms including my fingers. Some of the unintended or unplanned impacts included the inability to successfully type at times (more on that later) and what feels like cramping in my hand when holding a mouse for too long. I was using a nice Logitech mouse for most of my work. I have also used the Surface Arc mouse from Microsoft. I liked Arc mouse because it traveled very well because you can flatten it. The Logitech mouse and split keyboard were a part of my standard home office setup.

An older picture of my setup with the Logitech mouse and keyboard

As a condition worsened, I found myself struggling to use a mouse longer than half hour to an hour at a time. I would work with the mouse and then eventually I would start unintentionally clicking the buttons and was unable to actually move it. The level of frustration caused by working that way is pretty high.

Large trackpad try out

Because the problem appeared to be around the fact that I was holding the mouse, my first thought was to try a larger trackpad. This would allow me to use both hands and fingers effectively to manipulate the mouse on the computer. What I found was it just transferred the problem. My lack of finger control and the cramp in my hand still existed while using the trackpad.

Enter the roller bar mouse

A friend of ours works with hand specialists back in Minneapolis. She suggested we check out something called the RollerMouse. It’s an interesting tool. (One thing to note here is that all of these tools cost something. Trying to find the right tools to work is not inexpensive. Hopefully some of this information will help you save some money. ) I went online and did some research about how it’s supposed to help. A lot of the reviews talked about folks with arthritis or other similar conditions which impact their ability to work with a mouse for an extended period of time. Including the fact that they could have shoulder issues and so on. That being said, I decided to give it a try.

The RollerMouse Red that I purchased

It is a completely different way to work with the mouse functionality on your computer. Many of the reviews talked about the two-week learning cycle that was required to be effective. I found that I was affective using the mouse within a couple of hours. I do think this was related to the fact that I struggle so much to work with a traditional mouse or trackpad. The roller bar effectively supports a three-screen solution including two 34-inch monitors. The side-to-side motion of the bar navigates seamlessly from one screen to the other.

Beyond that the buttons are awesome! Naturally there are the left and right click buttons in the center of the bar. The bar itself functions as a left click which is a very natural function when working with the mouse. There is the scroll wheel in the middle which works like a scroll wheel on a normal mouse. Now it gets interesting. At the center in the bottom is a double click button. I did not know if I’d actually use this, but I find myself using it especially when switching hands while using the mouse. The steepest part of my learning curve has been effectively using the buttons. I must keep in mind that the cursor placement is where the clicks will occur, not the last place I had clicked. Up next, they have the copy and paste buttons right above the left and right clicks. In a later blog I will talk about speech to text functionality but having shortcut buttons for copy and paste means I did not have to do the Ctrl+C or Ctrl+V patterns on the keyboard. Which is great! All the buttons are programmable. This means you can change them if you have a better pattern that works for you. So far, I’ve only changed one button, the scroll button click. I use it to turn on dictation in Word.

It changed the way I work

To say that this has changed the way that I work and allowed me to work longer is the understatement of the year. I am still working through other tools and devices to continue to help me be productive. But to date this is by far, the best investment I have made. If you’re experiencing issues manipulating a mouse in the traditional fashion or anything that requires you to potentially either switch hands to give yourself a break or just because it’s hard sometimes to use a mouse and move it, this is a great solution for you. The mouse is stationary and has a nice wrist pad for you to work on. The ability to switch hands allows me to get the break I need on either hand at any given time. With this mouse I am able to stay more productive than I ever thought I was going to be able to win the started. As you can see, I’m a real fan.

My new desk set up with the RollerMouse in the middle

For those of you that aren’t dealing with issues and are wondering should you use it? If you want to experience a different method of working with a mouse and get rid of moving around mouse is on your desk or touching a trackpad this is a great solution. I personally think I would have fallen in love with this solution much earlier had I known about it. Now you know about it!

I love to hear from you if this was helpful and if you’ve decided to give this a try. For those of you suffering with carpal tunnel or arthritis come on give this a serious consideration. It is on the pricey side, but I will tell you in this case it may be worth it.

The Impact of Change

I started this summary on LinkedIn. Check out that article here. I only used LinkedIn to introduce what is happening, if you’re interested in learning more keep reading.

Kristyna Hughes

First, I have to say thank you to my daughter Kristyna, who has been contributing regularly to our Data on Wheels blog in my absence. I’m amazed at quality and depth of the content she has provided to the community including blogs on the tabular object model and C#. I’m glad she is able to provide great content for the data community.

3Cloud has been a key partner in my journey so far. Early in the diagnosis we were not sure if this was ALS. Frankly, we are still not sure if it will lead to that eventually. 3Cloud has stepped in and helped me find a place where I can contribute and support our teams as we continue to grow our business in data and analytics. For that I can say I am supremely thankful. This has allowed me to adapt to my new circumstances and continue to contribute in meaningful way.

Now we get a little more personal, I can say nothing but great things about my wife, Sheila. She has had to step up as a caregiver in ways that we were not expecting. Sheila has been my rock through this as well as my support when I really needed it. From helping me get ready for the day to keeping me going through the day, she has been magnificent through it all. What we do not know is what God has planned for us or what the next stage will be for me as we move forward. We can only trust him every day for what is next.

As a part of this process, we have moved from Minnesota to Kentucky to be closer to my family. That move was interesting. We had a lot of ups and downs as we went along, and I have my adult children to thank for a lot of the help throughout those weeks. We also had many friends help us with packing and prepping because I could not do much at all. They were very gracious and gave us time on their weekends and helped us make this move. When we got to Kentucky my kids did the lion’s share of unloading which was unexpected. With their help we were able to get moved in quickly. They have helped with painting, clean up, and unpacking throughout it all. I have watched my wife and daughters pick up new skills to fill the void where I would have normally done the work. It’s pretty impressive really. Other family members have also stepped up to help as needed which has been great as well.

We are in Frankfort

So, what does that mean for the blog? Well, Kristyna will continue to write on topics that she loves. I will contribute when I can on technical content. But I will also be providing reviews of various tools and technologies I have tried or used through this journey. I hope that some of these reviews will help some of you out there who may have similar or related issues. And maybe someone will find a tool that will be super helpful to them. I look forward to sharing this journey as we move forward.

If you made it this far, thank you for giving me some of your time and lending me your ear. If you want to know more about the personal side of this journey, you can check it out on CaringBridge. I look forward to getting back out in community and connecting with many of you along the way.

Power BI: Calculating Network Days in M Custom Columns

Knowing the days between events is a fairly common reporting request because a lot of reporting is created to track SLA’s (service level agreement) and other KPI’s (key performance indicators). While getting the days between two dates is fairly easy to achieve, they tend to follow up and ask how many week days there are between two timed events. For example, one company may have a SLA to ship an order within three week days of the order being placed or else a discount is applied to the order. In this case, I would highly recommend that the company have software that calculates these days in the background and stores the actual week days between order date and ship date in a database. Unfortunately, many companies create policies like this without considering future reporting needs and these values have to be calculated on the backend.

Please note, this blog will not cover how to calculate business days (week days that are non-holidays) because that should be done upstream from Power BI in either an application or SQL stored proc that can get/set holidays dynamically (the date of Easter changes every year). International clients often have different holidays which can infinitely complicate the definition of a business day. Also, many coding languages have built in functions to get business days while Power BI does not (sadly, the Excel NETWORKDAYS() function is not currently in Power BI) . So for this, we will stick to excluding weekends and getting a count of the week days between two events.

The Final Equation

To make it easier to read, SD means start date and ED means end date. SWD will mean the work week day of the start date and EWD means work week day of the end date. For example, if the order date was May 2nd 2022 and the ship date was May 4th 2022, then the SD = 5/2/2022, SWD = Monday/day 2 of the week, ED = 5/4/2022, and EWD = Wednesday/ day 4 of the week. But let’s say the SD was May 7th which is a Saturday. We would adjust this to be Friday/ day 6 of the week. I’ll put code below to show how to adjust the weekends to Friday before and how to adjust to the Monday after, but the GitHub file will align weekends to the Friday before.

Week days between = ( if SWD > EWD then (5 + EWD – SWD) else (EWD – SWD)) + (5 * ( Actual Days Between /7 rounded down))

In plain English: the week days between two dates starts by looking at if the start date is earlier in the week than the end date. If the start date is later in the week than the end date, then add five to the difference between their day of the week (for example, end day of Monday to start day of Wednesday would be 5 + 4-2). If the start date is earlier in the week than the end date, then take the difference between those two days of the week (start day Monday to end day Wednesday would be 4-2).

Next, take the actual days between your end and start date including weekends, divide that by seven then round down. That will give you the number of full weeks between your two days. Multiple that number by 5 to represent the five days within each of those weeks.

Finally, add those two numbers together to get your week days between the start and end dates.

Building the Variables

To create that final equation, we will need to build a few extra columns in the Power Query Editor. For each one, we will click on “Transform Data”, go to the “Add Column” tab, then select “Custom Column”. This will open a window to type in some custom M code (yay another coding language!).

Don’t worry though, I have ya covered with the code you need for this demo. To start, identify your columns with your start date and end date. For this demo, we will have Start Date = Order Date and End Date = Ship Date. The code below will use StartDate and EndDate for these fields, but the GitHub file will use Order Date and Ship Date.

The code below will turn Saturday and Sunday into Friday:

Start Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 1 
then Date.AddDays([StartDate],-2) else
if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 7 
then Date.AddDays([StartDate],-1) else [StartDate])
,Day.Sunday) +1

End Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 1 
then Date.AddDays([EndDate],-2) else
if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 7 
then Date.AddDays([EndDate],-1) else [EndDate])
,Day.Sunday) +1

The code below will turn Saturday and Sunday into Monday

Start Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 1 
then Date.AddDays([StartDate],1) else
if (Date.DayOfWeek([StartDate],Day.Sunday)+1) = 7 
then Date.AddDays([StartDate],2) else [StartDate])
,Day.Sunday) +1

End Date Work Weekday = 
Date.DayOfWeek(
(if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 1 
then Date.AddDays([EndDate],1) else
if (Date.DayOfWeek([EndDate],Day.Sunday)+1) = 7 
then Date.AddDays([EndDate],2) else [EndDate])
,Day.Sunday) +1

Here’s the formula with some comments added in:

Work Weekday = 
Date.DayOfWeek( //sets us up to get the day of the week at the end of the formula
(if (Date.DayOfWeek([Date],Day.Sunday)+1) = 1 //if it's Sunday 
then Date.AddDays([Date],-2) else //then go back two days to get Friday's date
if (Date.DayOfWeek([Date],Day.Sunday)+1) = 7 //if it's Saturday
then Date.AddDays([Date],-1) else [Date]) //then go back one day to get Thursday's date, otherwise give me the date 
,Day.Sunday) +1 //now give me the day of the week for the date left from the previous if statements

Alrighty, the next building variable we will need for our equation is the actual days (including weekends).

Days Between = Duration.Days([EndDate]-[StartDate])

In our case though, there is a possibility that the orders have not shipped. To handle any errors where there is no EndDate or StartDate, use the equation below:

Days Between = if [EndDate] is null then null else
if [StartDate] is null then null else
Duration.Days([EndDate]-[StartDate])

Okay great, now we can put it all together using our new fields!

Weekdays Between = 
( if [Start Date Work Weekday] > [End Date Work Weekday] 
then ( 5 + [End Date Work Weekday] - [Start Date Work Weekday] )
 else ( [End Date Work Weekday] - [Start Date Work Weekday] )) 
+ ( 5 * ( Number.RoundDown ( [Days Between] / 7 )))

Boom all done! You made it through a lot of complicated M code, nice work! Below is the M used in the GitHub file so you can see how it will look in the advanced editor.

let
    Source = Excel.Workbook(File.Contents("C:\Users\KristynaHughes\OneDrive - DataOnWheels\GitHub\AdventureWorks Sales.xlsx"), null, true),
    Sales_data_Sheet = Source{[Item="Sales_data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sales_data_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SalesOrderLineKey", Int64.Type}, {"ResellerKey", Int64.Type}, {"CustomerKey", Int64.Type}, {"ProductKey", Int64.Type}, {"OrderDateKey", Int64.Type}, {"DueDateKey", Int64.Type}, {"ShipDateKey", Int64.Type}, {"SalesTerritoryKey", Int64.Type}, {"Order Quantity", Int64.Type}, {"Unit Price", type number}, {"Extended Amount", type number}, {"Unit Price Discount Pct", Int64.Type}, {"Product Standard Cost", type number}, {"Total Product Cost", type number}, {"Sales Amount", type number}}),
    #"Added Order Date" = Table.AddColumn(#"Changed Type", "Order Date", each Date.From( Text.Middle(Text.From([OrderDateKey]),4,2)&"/"& Text.End(Text.From([OrderDateKey]),2)&"/"& Text.Start(Text.From([OrderDateKey]),4)),type date),
    #"Added Ship Date" = Table.AddColumn(#"Added Order Date", "Ship Date", each Date.From( Text.Middle(Text.From([ShipDateKey]),4,2)&"/"& Text.End(Text.From([ShipDateKey]),2)&"/"& Text.Start(Text.From([ShipDateKey]),4)), type date),
    #"Added Order Weekday" = Table.AddColumn(#"Added Ship Date", "Order Date Weekday", each Date.DayOfWeek(
(if (Date.DayOfWeek([Order Date],Day.Sunday)+1) = 1 
then Date.AddDays([Order Date],-2) else
if (Date.DayOfWeek([Order Date],Day.Sunday)+1) = 7 
then Date.AddDays([Order Date],-1) else [Order Date])
,Day.Sunday) +1,Int64.Type),
    #"Added Ship Weekday" = Table.AddColumn(#"Added Order Weekday", "Ship Date Weekday", each Date.DayOfWeek(
(if (Date.DayOfWeek([Ship Date],Day.Sunday)+1) = 1 
then Date.AddDays([Ship Date],-2) else
if (Date.DayOfWeek([Ship Date],Day.Sunday)+1) = 7 
then Date.AddDays([Ship Date],-1) else [Ship Date])
,Day.Sunday) +1,Int64.Type),
    #"Added Days Between" = Table.AddColumn(#"Added Ship Weekday", "Days Between Order and Ship", each if [Ship Date] is null then null else if [Order Date] is null then null else Duration.Days([Ship Date] - [Order Date]),Int64.Type),
    #"Added Weekdays Between" = Table.AddColumn(#"Added Days Between", "Weekdays From Order to Ship Date", each if [Days Between Order and Ship] is null then null else
( if [Order Date Weekday] > [Ship Date Weekday] then (5 + [Ship Date Weekday] - [Order Date Weekday] ) else ( [Ship Date Weekday] - [Order Date Weekday] )) + (5 * (Number.RoundDown([Days Between Order and Ship]/7))),Int64.Type)
in
    #"Added Weekdays Between"

Additional Resources

Power BI Adding Dynamic Hierarchies – XMLA, TOM, C#

This post is a continuation of my adventure into the Tabular Object Model and how we can use it to make Power BI scalable and incorporate it into existing .NET applications. Quick refresher, the Tabular Object Model can be accessed through the XMLA endpoint in Power BI Premium workspaces. My previous posts have covered code around adding, deleting, and adjusting columns and measures, but this one will address how to manipulate hierarchies.

Power BI hierarchies are a powerful and easy way to enable end users to dig deeper into their visuals and data. While hierarchies can be a useful resource for self-serve analytics, maintaining hierarchies can be a pain as new levels get added or removed. Thankfully, if you have Power BI premium you can use the XMLA endpoint to add code into existing .NET applications to dynamically add or remove levels from hierarchies as they are created/removed in your application.

Unfortunately, while we can manipulate, add, and delete hierarchies and their levels, visuals already containing the hierarchy will not be automatically adjusted with any new levels/ordinals.

Microsoft TOM Documentation

If you are new to using C# and the Tabular Object Model (TOM), please check out the previous blog post (https://dataonwheels.wordpress.com/2021/10/15/power-bi-meets-programmability-tom-xmla-and-c/) for both an introduction to the topic and detailed instructions on getting the C# portion of this demo stood up. Please reference the DataOnWheels GitHub page for sample PBIX files and C# packages, but note you will need a Power BI Premium workspace with XMLA endpoint write-back enabled in order to run this entire demo.

Power BI Hierarchies

To start out, let’s make sure we understand the components of a hierarchy that we will need to replicate using our TOM script. In the Power BI Desktop app, creating a hierarchy is fairly simple. For example, let’s say I want to have end users drill down from category to subcategory. To do this, I would hover over the category column then click on the three dots next to the category column and select “create hierarchy”.

Next, go to the subcategory column and you’ll notice a new option called “add to existing hierarchy”. Select our newly created hierarchy (default will be named after the top level in the hierarchy), and it will add subcategory underneath category within the hierarchy. Pretty neat stuff but also very manual.

From this, we can see that there are a few components to a hierarchy that we will need to address in our TOM script:
1. Name
2. Levels
3. Order of levels (Ordinal)
4. Column in each level
5. Name of level

Using TOM to See Hierarchies, Levels, Ordinals, and Source Columns

Now that the data model contains a hierarchy, we can publish it up to a Premium enabled workspace in the Power BI service and see it using our TOM script. I won’t go into details on building out this script from scratch, so please reference this blog post for a complete walk through on connecting to your workspace and building a simple C# application to use with this demo.

To list out the hierarchies in the data model, you will need something like this script in your code (entire zip file is in the DataOnWheels github for reference):

  // List out the hierarchies in the product table
            foreach (Hierarchy hierarchy in table_product.Hierarchies)
            {
                Console.WriteLine($"Hierarchies: {hierarchy.Name}");
            }

And poof there it is! Our Category Hierarchy! Next we will have our script list out the levels within the hierarchy.

// List out the levels in our Category hierarchy
            Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy"];
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Levels: {level_categoryhierarchy.Name}");
            }

Great, and the next piece will be the ordinal, or the order that the hierarchy levels should be placed. I’m going to adjust the last code so now it will tell us the ordinal/order of each level before it gives us the name. Notice that this starts at 0, not 1.

// List out the levels in our Category hierarchy
            Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy"];
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name}");
            }

And for our final piece of the puzzle, the column name that this level of the hierarchy comes from.

// List out the levels in our Category hierarchy
            Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy"];
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name} from {level_categoryhierarchy.Column.Name}");
            }

Editing a Hierarchy Using TOM

Let’s switch it up and begin editing our existing hierarchy by changing the name of the hierarchy, the name of the levels, the source columns, and swap the ordinances. Typically you will not need to do any or all of these things, but it may be useful in rare use cases.

To start, we will rename the hierarchy itself. Now it will be important to reference the Category Hierarchy by the lineage tag after we rename it. The lineage tag won’t change even after you change the name property of the hierarchy itself. Please note your lineage tag will be different from mine, so run the script that will list the lineage tag next to the name (below) first then replace that portion in the rest of the code where there are references to the reference tag.

// List out the hierarchies in the product table
            foreach (Hierarchy hierarchy in table_product.Hierarchies)
            {
                Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag = {hierarchy.LineageTag}");
            }
            // List out the levels in our category hierarchy
            Hierarchy hierarchy_category = table_product.Hierarchies.FindByLineageTag("9aeadacd-d48d-48cb-948f-16700e030fe7");
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name} from {level_categoryhierarchy.Column.Name}");
            }

In the Power BI service, we can check if this rename effort was successful by entering edit mode.

Success! Let’s try changing the name of a level next then swap the order around.

 //Hierarchies:
            //Editing an existing hierarchy originally called Category Hierarchy
            {
                hierarchy_category.Name = "Category Hierarchy Rename Test"; //this renames the hierarchy, note the lineage tag will remain unchanged
                Console.WriteLine($"Category Hierarchy Renamed");
            }
            //Editing an existing hierarchy level 
            Level level_Category = hierarchy_category.Levels.FindByLineageTag("fe12a6fc-1023-43f9-bfdc-c59f65435323");
            Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("fbb4aa00-35dc-4490-bc40-3190b354ea54");
            {
                level_Category.Name = "Category Test";
                level_Subcategory.Name = "Subcategory Test";
                Console.WriteLine($"Category Hierarchy Levels Renamed");

Awesome! Okay now for the final piece of the puzzle – switching the ordinances to make subcategory the top of the hierarchy. Note, you will need to start at level 0. Also, if you are experiencing errors in saving the model, make sure you are out of edit mode in the Power BI Service. While it’s helpful to be in that mode to see your changes, it will be impossible to make additional changes via XMLA until you are out of it.


            //Hierarchies:
            //Editing an existing hierarchy originally called Category Hierarchy
            {
                hierarchy_category.Name = "Category Hierarchy Rename Test"; //this renames the hierarchy, note the lineage tag will remain unchanged
                Console.WriteLine($"Category Hierarchy Renamed");
            }
            //Editing an existing hierarchy level 
            Level level_Category = hierarchy_category.Levels.FindByLineageTag("fe12a6fc-1023-43f9-bfdc-c59f65435323");
            Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("fbb4aa00-35dc-4490-bc40-3190b354ea54");
            {
                level_Category.Name = "Category Test";
                level_Category.Ordinal = 1;
                level_Subcategory.Name = "Subcategory Test";
                level_Subcategory.Ordinal = 0;
                
                Console.WriteLine($"Category Hierarchy Levels Renamed & Reordered");
            }

            // List out the levels in our category hierarchy
            foreach (Level level_categoryhierarchy in hierarchy_category.Levels)
            {
                Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name} Lineage Tag: {level_categoryhierarchy.LineageTag} from {level_categoryhierarchy.Column.Name}");
            }

Boom now we have proven we can reorder the levels as well as rename them and the hierarchy itself.

Adding Hierarchy Levels & Hierarchies via TOM

Now we are finally ready to add a brand new level into our hierarchy! In the sample data, the model column should go below subcategory in my hierarchy. To add a level to the hierarchy we will need a few items – the name of the level, the ordering of the level, and the column it should reference. You can add a lineage tag as well (Power BI will not add one unless you made this level in the desktop application). Don’t forget to add the level you’ve created to the hierarchy or else it will stay in cache and never get added.

            //Hierarchies:
            //Editing an existing hierarchy originally called Category Hierarchy
            {
                hierarchy_category.Name = "Category Hierarchy Rename"; //this renames the hierarchy, note the lineage tag will remain unchanged
                Console.WriteLine($"Category Hierarchy Renamed");
            }
            //Editing an existing hierarchy level 
            Level level_Category = hierarchy_category.Levels.FindByLineageTag("fe12a6fc-1023-43f9-bfdc-c59f65435323");
            Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("fbb4aa00-35dc-4490-bc40-3190b354ea54");
            {
                level_Category.Name = "Category";
                level_Category.Ordinal = 1;
                level_Subcategory.Name = "Subcategory";
                level_Subcategory.Ordinal = 0;
                
                Console.WriteLine($"Category Hierarchy Levels Renamed & Reordered");
            }
            //Adding a new level to the hierarchy if it doesn't already exist
            if (hierarchy_category.Levels.ContainsName("Model"))
            {
                Console.WriteLine($"Hierarchy Level Exists");
            }
            else 
            {
                Level level_Model = new Level()
                {
                    Name = "Model",
                    Ordinal = 2,
                    Column = table_product.Columns.Find("Model")
                };
                hierarchy_category.Levels.Add(level_Model);
                Console.WriteLine($"Hierarchy Level Added");

Let’s try making our own hierarchy from scratch. To review, we will need to have a name for our new hierarchy, the name of the levels, the order of the levels, and the column of the levels. We will also need to explicitly add the new hierarchy to the model then add the levels to that hierarchy.

//Add a new hierarchy if it doesn't already exist
            if (table_product.Hierarchies.ContainsName("New Hierarchy"))
            {
                Console.WriteLine($"New Hierarchy Exists");
            }
            else
            {
                Hierarchy hiearchy_new = new Hierarchy()
                {
                    Name = "New Hierarchy",
                };
                table_product.Hierarchies.Add(hiearchy_new);
                Console.WriteLine($"Hierarchy Added");

                Level level_one = new Level()
                {
                    Name = "Model",
                    Ordinal = 0,
                    Column = table_product.Columns.Find("Model")
                };
                Level level_two = new Level()
                {
                    Name = "Product",
                    Ordinal = 1,
                    Column = table_product.Columns.Find("Product")
                };

                hiearchy_new.Levels.Add(level_one);
                hiearchy_new.Levels.Add(level_two);
                Console.WriteLine($"Levels added to new hiearchy");
            };

Awesome! Now we know we can programmatically add hierarchies, add levels, rearrange levels, rename levels, and point levels to different columns. This won’t apply to many use cases of Power BI, but for those of you embedding a Power BI solution into your application, this should offer greater flexibility and integration with your existing .NET applications.

Additional Resources:

Power BI: Creating an Info Button with UNICHAR()

Reports are only as useful as they are easily understandable. When making reports for executives and other business leaders, it is vital to ensure the context and insights of the report are easy to understand at a glance. As cultivators of data, we have a responsibility to make sure it is used and interpreted correctly otherwise data can quickly be used to come to incorrect conclusions. When users don’t understand data, they often distrust any data-driven decisions and go back to using instinct to make important business decisions.

While it’s important to give context to reports, the method of doing so may seem cumbersome. Some options include explaining the report to every new user or taking up valuable report space with explanations and contexts. For end user experience, it’s often best to replicate something they are already familiar with like a webpage. Many websites contain options for giving additional details to curious end users, typically they signal these resources with an “i” or information icon. We can replicate that functionality in Power BI by using UNICHAR characters and a hidden page.

The steps below will walk through how to add an information icon to the report, making a tooltip page containing your additional information, and enabling the tooltip to allow users to hover over the icon and see the information.

1. Make the following measure in your measures table:
Info Button = UNICHAR(128712)

2. Create a card visual and add this measure to it. You’ll see the following icon:

3. Create an additional page with a text box containing your additional information.

4. Hide this information page and configure it to be used as a tooltip. You may also want to resize it so that when users view it as a tooltip on the info icon it will cover the entire report. To hide the page, right click on the page name and select “Hide Page”. To get to the page configurations, you’ll need to select a blank section of the page (this avoids any visual configuration settings) and select the format paint brush. For resizing, I recommend setting it as a custom type then adjusting your pixels until it nearly fits your text box. Note: it will automatically align top left, so it’s best to align your text box to top left so as you resize you don’t lose your text box.

5. Add this info page as a tooltip for the card visual containing the info button. To do this, you’ll need to select your card visual, go to the format pane, and turn tooltips on. Once tooltips are on, you can select the type of report page then select the page that you made earlier with your additional information.

Now when you hover over the icon, the tooltip will display your additional information. In the example below, I’ll mess with the pixels more on the info page until it’s the appropriate size for the amount of information I want to convey.

And that’s all there is to it! One valid alternative to this is to make a bookmark that would lead end users to the information page. Personally, I prefer this tooltip method so end users do not lose their place within the report and it is small enough to be put on every page of the report.

Feel free to check out the PBIX used for this blog post in the Data On Wheels GitHub Repo.