Category: Reporting

Power BI Cruise Recap – Deep Data Dives

On By Kristyna HughesIn C#, TOM, and Power BI, General, Power BI, Power Query, Reporting, Speaking1 Comment

Thank you so much to those of you who were able to attend this three-hour, C# packed session! The Power BI Cruise was an incredible conference, and I cannot recommend it enough if you’re looking for an opportunity to dig deep on Power BI topics with other passionate individuals. From the first ever Power BI bingo to a three hour session on TMDL from Mathias himself, this was a trip that will stick with me for a while. In this post, I’ll include notes from my session and the sessions I attended plus links to the GitHub. Let me know if anything really piques your interest and you’d like to see more detailed blog posts about it!

  1. My Session – Power BI, C#, and TMDL!
  2. TMDL & Source Control – Mathias Thierbach
  3. Hacking the Visuals – Stepan Resl
  4. Ask Me Anything – Jeroen (Jay) ter Heerdt

Huge shout out to the other speakers and of course the incredible organizers who stuck with this amazing vision through the pandemic and made it happen. Thank you all so much for making this event full of passion and unparalleled learning. I don’t know how you all made this happen without sponsors, but it was truly an incredible event. So thank you again Asgeir, Erik, Johan, and Just!

My Session – Power BI, C#, and TMDL!

Now you may have noticed I give this session – Power BI Meets Programmability – quite often. Bringing programmability to the world of Power BI is something I greatly enjoy, but this session was truly unique. Having three hours to take a deep dive into what C# can do for Power BI development was the opportunity of a lifetime. With two more hours than usual, we explored creating calculation groups in this session for the first time as well as creating automatically generated set of measures based on data types. Not only that, but the attendees were good sports and stuck around for an extra half hour to create calculation groups in C# using TMDL! I plan on doing a very detailed blog on this in the future, but the GitHub folder for this conference (link below) contains TMDL code live coded by the creator himself – Mathias Thierbach! The script contains a section that will create a TMDL version of your data model, allow you to interact with TMDL files within your solution, and publish those changes back to the data model. It blew my mind, and I hope it blows yours as well!

Additional Resources

The final code can be found here: https://github.com/Anytsirk12/DataOnWheels/tree/main/Power%20BI%20Cruise.

TMDL & Source Control – Mathias Thierbach

Speaking of TMDL, this conference hosted the incredible Mathias Thierbach for a session on TMDL & Source Control. Below are my notes from that session, but if you ever have a chance to hear him speak on the subject I highly recommend it. The notes below are in bullet point format for now, as I dig further into this language I look forward to writing more detailed blog posts about this incredible language and what we can do with it.

  • Key goals of TMDL: readable, editable, and allows collaboration
  • There will be a VSCode extention for TMDL coming soon (we were given a special preview in the session) that will have syntax highlighting for both DAX and M as well as TMDL
  • Shortcut to get to the TOM docs: goto.pbi.tools/tom-docs or pbi.onl/tools
  • TMDL is completely case insensitive!
    • Default export of .tmd file is camel case
  • Boolean properties: you only have to say the Boolean property to enforce the default value
    • To explicitly set it, syntax is:
      property: true/false
  • No matter how many times you serialize TMDL, the order of the objects will always be the same
    • Tables: partitions, calc groups, columns, hierarchies…
    • You can reorder objects
    • You can reorder objects to your specifications (aka no longer alphabetical)
  • Meta data IsParameterQuery = true makes it a parameter
  • Ordinal is not a table property in TOM, but you can create one in TMDL! It won’t show up in this order in Desktop…yet. But it will allow consistent order. It’s a weak ordinal, in cases of conflict it won’t cause any errors. It should default to alphabetically on conflict.
  • Indentation for expressions is not white space sensitive (DAX and M)
    • It finds a shared whitespace to the left to create indentation in the final model
  • Indentation only matters for nested objects
  • To fold levels in VS Code use Ctrl + k + [level_number]
  • To unfold levels in VS Code use Ctrl + k + j to unfold all levels
  • Unicode is supported
  • Shared expressions = parameters
  • Preview 1 (what we have), perspectives can’t be read back but can create them
    • Will be fixed in Preview 2
  • 4 previews total, goal is for GA at end of the year
  • tableName.columnName etc.
    • If there are spaces, use single quotes as a delimiter
  • Description is added by using /// text on top of the object declaration. Can be multi-line and line break is maintained.
  • No comment support but you can comment within M and DAX
    • Triple ‘’’[formula] ‘’’ will be a safe way to mark M/DAX expression sections. Likely coming in preview 3 or 4 later this year
  • Language spec will be published
  • Default properties are available to find in Rui’s documentation
    • Default property is what is after the = for the object
    • This can be found on the main TMDL overview page

How to create a TMDL folder from Tabular Editor (need 2.18.1 version or later)

Open the folder you’ve created within VS Code.

Hacking the Visuals – Stepan Resl

Hacking = use various available means to get better insights from data.

This session absolutely blew my mind. Stepan does an incredible job showing the art of the possible by hacking into expression properties within tabular editor as well as calculation group formatting. We had an awesome time connecting at this conference and I hope to see him again at future conferences! In the meantime, below is a link to his GitHub as well as a blog post that covers a lot of the same material as his session. Be prepared to have your mind blown on this one.

Stepan’s GitHub for this session:

https://github.com/tirnovar/public-speaking/tree/main/Power%20BI/Power%20BI%20Cruise%20-%202023

Additional blog post by Stepan on this subject: https://datameerkat.com/conditional-formatting-calculation-groups

  • For bar charts – to easily highlight what is over target, change the color within the bar at the target with grey below the target
  • Visual Vocabulary = a great reference for chart types
    • Ft.com/vocabulary
  • / used in a format string will escape the wildcard characters
  • Keep in mind audience, green is not positive in Japan
  • topProductByLocation = TOPN(1, ALLSELECTED(Products[ProductName]),[# Total Quantity])
  • # sold quantity by Top Product = VAR _product = [topProductByLocation] RETURN CALCULATE([# Total Quantity], Products[Product name] = _product
    • This allows us to use a measure as a filter by precalculating it in a variable
  • Best whitespace option is to use UNICHAR 8203, it’s a very very small dot
  • Color picker: Just Color Picker
  • Use smart text in subtitles in charts to tell people when we hit a target or how close we are
  • You can use format to change numbers to text with a thousands delimiter! See the subtitle measure
  • You can use ; within format to do an if statement! BUT don’t forget to escape any special characters using \
    • Example: “Sales target was set to ” & FORMAT(_target,”#,,,,,,,,,,#”)
      & ” and we have sold ” & FORMAT(_sold,”#,,,,,,,,,,#”)
      & ” which means we ” & FORMAT(_sold-_target,”\have;\haven’t”) & ” fulfilled our target.”
    • In this case “h” is a special character that is trying to get an hour value and will show 0 if you don’t escape it
  • If you create conditional formatting in bar charts, the colors will stay if you switch to a line chart even though conditional colors are not available in the UI
    • Same thing happens if you have an x constant line in a line chart and switch to bar chart
  • You can customize spacing below titles and subtitles.

I’ve added the final PBIX from this session to the GitHub, keep in mind a lot of the features that make it possible are only visible through tabular editor: https://github.com/Anytsirk12/DataOnWheels/blob/main/Power%20BI%20Cruise/Hacking%20the%20Visuals.pbix

Ask Me Anything – Jeroen (Jay) ter Heerdt

Meeting Jay and chatting during various free time on the ship was definitely a highlight of the trip. He’s incredibly knowledgeable on Power BI (especially the elusive DAX language), and has a deep understanding of the Microsoft strategy on new feature creation. It was a pleasure getting to laugh and brainstorm together, and I can’t wait to hang out again at future conferences. Below are some questions that were asked during the AMA session as well as answers provided and links I found while Jay was talking.

  • Why can’t we use RLS and USERELATIONSHIP() together?
    • RLS blocks USERELATIONSHIP() since USERELATIONSHIP() can (but doesn’t always) go around the RLS relationship and creates a security risk
  • Different people own different visuals which is why the options and customizations vary so widely
    • Miguel Myers did a session on the future of visuals in Power BI. Huge plans with him on board to own visuals and align everything
    • There is a linked in group called PBI Core Vision, put comments there on what can be improved in the future. Twitter and LinkedIn
  • When we change the hierarchy name or order via XMLA, it now breaks the visual. Can we get this to update in the visual in the service with adjusted level orders
    • SEND TO JAY
    • Also ask him about Top N + Others
  • Tableau is the primary competition for PBI, Looker is up and coming as Google, and third is Qlik because it’s still getting new clients in Europe
    • These are taken into consideration when making a case for new items
    • Cannot blatantly copy items
    • Tableau has specific people in their agreement that cannot even look at Tableau (can only look as a user, not at the debugger nor dll file)
  • In the future, hoping to have functions that could be shared within a community for DAX
  • Could we get Rulers or aligned grid lines in PBI Desktop?
    • Rosie or Ree-ann would own this
    • Can we customize grids?
    • Not part of visual team, it’s placed under on-boarding
  • Advancements to API?
    • Hardening of PBIX will help with getting better Scanner APIs to get deeper API’s (visual level and page level details on what’s available within the tenant)?
  • DataZen got killed and unsure why
  • Jay would investigate how to get rid of filter direction in DAX
  • Why can I not put a measure name in smart narratives?
    • You can do this by creating a shape and using fx to put in the text you want

Power BI: Calculating Network Days in M Custom Columns

On By Kristyna HughesIn General, Power BI, Power Query, Reporting1 Comment

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#

On By Kristyna HughesIn C#, TOM, and Power BI, General, Power BI, Reporting1 Comment

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 DAX Getting the Value of Previous Non-NULL Row

On By Kristyna HughesIn 3Cloud, General, Power BI, Power Platform, Reporting3 Comments

If you are an avid report designer or user, you may have wanted to see the percent difference between one row and the previous one. Sounds easy right? Well let’s break it down. Percent difference follows a consistent formula: (current value – previous value)/previous value. Current value? Easy peasy. Previous value? That’s where this blog comes into play. It’s fairly simple to get the previous month value or previous date period value because Power BI can tell it exactly what the filter would be (September minus one month is always August ). However, there may not have been any invoices in August, so how can we compare September with the last month that had an invoice?

Let’s set the scene for this example. A business user has requested to compare each day’s orders with the previous day they had orders. To get the previous day that had orders, we can create a measure called Last Day Order Quantity (see below). Within this measure, you’ll notice we use a variable to calculate the measure Order Quantity. By pulling this into the variable, it will save the row context for Order Quantity so we can make sure that we are only looking at rows that have orders. The next variable (LastDayOrdered) filters the ship date table to grab the last date where 1. there are orders (Order Quantity is not blank) and 2. the current day (aka the row we are currently on) also has orders. The final variable does the work of calculating our order quantity for the last day that contained orders. 

Last Day Order Quantity = 
VAR Orders = [Order Quantity] //grab the charge amount for this line
VAR LastDayOrdered = MINX(FILTER(ALL('ShipDate'),'ShipDate'[DaysAgo]>MAX('ShipDate'[DaysAgo]) &&[Order Quantity] <> BLANK() && Orders<>BLANK()),'ShipDate'[DaysAgo])
VAR LastDayOrders = CALCULATE([Order Quantity],FILTER(ALL('ShipDate'),'ShipDate'[DaysAgo]=LastDayOrdered))
RETURN  
LastDayOrders

As you can see in the screenshot above, there is a gap in ship dates between 1/25/2014 and 1/28/2014 and the last day order quantity pulls in the amount from 1/25/2014 (1) instead of grabbing it from 1/27/2014 (0). Sweet! Now that makes finding the % difference in order quantity very simple. Below is the full code from this example and also a more parameterized version with tons of comments to help you use it as needed. Happy coding!

Final Measure:

% Difference Order Quantity = 
VAR Orders = [Order Quantity] //grab the charge amount for this line
VAR LastDayOrdered = MINX(FILTER(ALL('ShipDate'),'ShipDate'[DaysAgo]>MAX('ShipDate'[DaysAgo]) &&[Order Quantity] <> BLANK() && Orders<>BLANK()),'ShipDate'[DaysAgo])
VAR LastDayOrders = CALCULATE([Order Quantity],FILTER(ALL('ShipDate'),'ShipDate'[DaysAgo]=LastDayOrdered))
RETURN  
DIVIDE(Orders-LastDayOrders,LastDayOrders,0)

Commented version:

% Difference = 
//Make a variable to grab the value of your measure at your current line (we will use this later)
VAR Desired_Measure = [Order Quantity]

//Now we need to grab the time where this desired measure was not null
//In my example, ship date is the column that I want to see the previous order quantity sliced by
VAR Last_Time_Measure_Had_Value = 
    // I use MINX here to ensure I get the previous day, not the next day. Feel free to flip this as desired.
    MINX(FILTER(ALL('ShipDate') 
        //this makes sure that I'm only grabbing ship dates that are before my current ship date.
        ,'ShipDate'[DaysAgo] > MAX('ShipDate'[DaysAgo])
        //this makes sure that the options for days ago only include ones that we had orders on, AKA the desired measure is not NULL for this day. 
        //DO NOT USE Desired_Measure here because desired_measure will only look at your current row and we are trying to look at all the rows to make sure we are grabbing a non-NULL one. 
        &&[Order Quantity] <> BLANK() 
        //this checks that the current row is not blank. 
        && Desired_Measure<>BLANK())
    //I need this variable to return the smallest number of days ago (hence the MINX at the beginning) that meets the criteria above
    //For your use, you should swap daysago with whatever field you are hoping to slice and dice by
    ,'ShipDate'[DaysAgo])

//This final variable calulcates my desired measure (order quantity) and filters my table for the last time that measure had a value.
VAR Last_Instance_Of_Calculated_Measure = CALCULATE([Order Quantity],FILTER(ALL('ShipDate'),'ShipDate'[DaysAgo]=Last_Time_Measure_Had_Value))

//Easy peasy now! We can take our current days orders - last day we had orders number of orders, divded by the last day we had orders number of orders to get the % growth day over day. Phewf!
RETURN  
DIVIDE(Desired_Measure - Last_Instance_Of_Calculated_Measure,Last_Instance_Of_Calculated_Measure,0)

Power BI Adding Translations to Rename Columns – XMLA, TOM, C#

On By Kristyna HughesIn Analysis Services, C#, TOM, and Power BI, General, Power BI, Power Platform, Reporting, Visual Studio1 Comment

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 demo stood up.

For the TOM and XMLA experts, imagine this. Your customer wants to dynamically rename columns without using the Power BI Desktop and would prefer all existing report visuals not get broken by the new name. Impossible? Not with TOM, XMLA, and translations within Power BI.

If you’ve ever tried to change a column name in a Power BI source, you’ve likely run into this error on any visuals that contained the renamed column. And when you hit that “See Details”, it will tell you the column that you simply renamed is no longer available for your visual.

So how do we get around that? Translations. Translations are typically used to translate report entities to other languages that will change depending on what language the end user has set on their browser. However, we can hijack this functionality to rename columns without having to impact the data model. It is a bit confusing on why this works, but imagine this: you build a Lego pyramid, but learn that one of the blocks needs to be changed from blue to green. Couple of options, you can take apart the entire pyramid (this would be akin to reopening the PBIX in Power BI Desktop and changing all of your visuals) OR you can take a green marker and color that blue brick green (adding a translation from blue to green).

If you don’t need to put this code into C#, the Tabular Editor is an excellent tool for adding translations to your data model (https://tabulareditor.com/creating-multilingual-power-bi-datasets/). However if you would like to programmatically update column names using C#, feel free to use the script below in your solution.

At a high level, here’s the hierarchy of entities used:
Workspace – Dataset – Data Model – Cultures – Object Translations
Workspace – Dataset – Data Model – Table – Column – Translated Properties

Note: There can only be one translated property per culture.

To add translations, we first need to set which culture this translation belongs in. For this example, we will use “en-US” because that is what default browser we want these names applied to. The code snippet below will list out all the cultures (aka website language codes) that are configured in this data model and list out all the translated objects (data columns in this case) that already exist.

After setting the culture/language, narrow down the column that this translation will be applied to and create a variable for the translation object. The translation object consists of two parts, the metadata object (column in this example) and the property of that metadata that we want to translate (caption in this example which is essentially display name).

Once we have these elements, we can check to see if this column already has a translation for this culture. If it does, this script will remove the old translation to allow for overwriting. If it does not, it will add the new translation to the culture within the data model.

And that’s it!

Here’s what it looks like in the service. Don’t forget to refresh your report page if you have it open for the new name to appear. There’s no need to refresh the dataset.

Full C# code:

using System;
using Microsoft.AnalysisServices.Tabular;



namespace PowerBI_TOM_Testing
{
    class Program
    {
        static void Main()
        {

            // create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
            string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOURWORKSPACE";
            string connectString = $"DataSource={workspaceConnection};";

            // connect to the Power BI workspace referenced in connect string
            Server server = new Server();
            server.Connect(connectString);
            // enumerate through datasets in workspace to display their names
            foreach (Database database in server.Databases)
            {
                Console.WriteLine($"ID : {database.ID}, Name : {database.Name}, CompatibilityLevel: database.CompatibilityLevel}, Last Updated : {database.LastSchemaUpdate}");
            }
            
            // enumerate through tables in one database (use the database ID from previous step)
            Model model = server.Databases["bb44a298-f82c-4ec3-a510-e9c1a9a28af2"].Model; 
            
            //if you don't specify a database, it will only grab models from the first database in the list
            foreach (Table table in model.Tables)
            {
                Console.WriteLine($"Table : {table.Name} IsHidden? : {table.IsHidden}");

            }
           
            // Specify a single table in the dataset
            Table table_product = model.Tables["Product"];

            
            
            // List out the columns in the product table
            foreach (Column column in table_product.Columns)
            {
                Console.WriteLine($"Columns: {column.Name}");
             }


            //Translations can be used to rename existing columns without rebuilding the model. This also updates any visuals that use that column. 
            // List of translations on the model
            foreach (Culture culture in model.Cultures)
            {
                Console.WriteLine($"Existing Culture: {culture.Name}"); 
            }

            // Let's get a list of the existing translations within the en_US culture
            Culture enUsCulture = model.Cultures.Find("en-US");
            
            foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations) 
            {
                Console.WriteLine($"Translated Object: {objectTranslation.Value}");
            }
            // Narrow down what column within this culture/language you would like to add the translation to
            MetadataObject dataColumn = table_product.Columns.Find("Description"); //this needs to always be the original column name within the data model.
            ObjectTranslation proposedTranslation = enUsCulture.ObjectTranslations[dataColumn, TranslatedProperty.Caption];

            // Only one translation per entity per culture.
            if (proposedTranslation != null)
            {
                Console.WriteLine($"Translation Exists for this Culture & Column combo");
                enUsCulture.ObjectTranslations.Remove(proposedTranslation); //need to remove the existing translation to overwrite it
                ObjectTranslation overwriteTranslation = new ObjectTranslation()
                {
                    Object = dataColumn,
                    Property = TranslatedProperty.Caption,
                    Value = "Blue"
                };
                enUsCulture.ObjectTranslations.Add(overwriteTranslation);
            }
            else
            {
                ObjectTranslation newTranslation = new ObjectTranslation()
                {
                    Object = dataColumn,
                    Property = TranslatedProperty.Caption,
                    Value = "Blue"
                };
                enUsCulture.ObjectTranslations.Add(newTranslation);
            }

            

            // List out the translations to see what they are now that we have run the script    
            foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
                {
                    Console.WriteLine($"Final Translated Object: {objectTranslation.Value}");
                }
            
model.SaveChanges(); //make sure this is the last line! 
       


        }
    }
}

Additional Resources:

– https://www.kasperonbi.com/setting-up-translations-for-power-bi-premium/
– https://tabulareditor.com/creating-multilingual-power-bi-datasets/
– https://www.sqlbi.com/tools/ssas-tabular-translator/
– https://docs.microsoft.com/en-us/analysis-services/tabular-models/translations-in-tabular-models-analysis-services?view=asallproducts-allversions
– https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.culture?view=analysisservices-dotnet
– https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.culture.objecttranslations?view=analysisservices-dotnet#Microsoft_AnalysisServices_Tabular_Culture_ObjectTranslations