Let’s set the scene. You’ve built a wonderful, useful, and descriptive report for your stakeholders with a variety of tooltips that offer deeper insights. They love the tooltips, and have requested more for other visuals, but when they get to the boardroom to present, they are unable to hover over any numbers without the tooltip blocking the context of that data point. Because they love the tooltips for their personal exploration, they don’t want them disabled permanently and have requested a way to temporarily turn them off for presentations. This blog was inspired by Steve Hughes (you may know him) complaining about “too many tooltips in the way of my data”.
This one is for you, Dad.
Tooltips are useful for personal exploration, but not shared exploration.
2. Open your Power BI report and create a duplicate page for the page you would like to disable tooltips on. To do this, right click on the page and select “Duplicate Page”.
3. Rename the new page something like “[original page name] – No Tooltip” by right-clicking on the new page tab. We are also going to hide this page.
4. Next, navigate to the View tab in the upper ribbon and select the “Selection” pane. This will bring up a list of all the visuals on your page.
5. Click to your first visual in the list with a tooltip and in the “Visualizations” pane navigate to the “General” setting to toggle “Tooltip” to off.
6. Repeat by clicking down the list in the selection pane and Power BI will keep opening the Visualization pane to the General tab for easy toggling. Pretty slick. Keep repeating until all your visuals have this toggle turned off. Keep in mind, not all your all your visuals have a tooltip like titles, text boxes, etc. To close the selection panel, select “Selection” in the upper ribbon to close that panel or select the “X” at the top of that panel.
7. Time to add our icon! Go to “Insert” in the top ribbon and select “Image” then choose the downloaded icon for magnifying glass without check box. This will signal to end users that there are no tooltips turned on for this page.
8. After resizing and positioning the magnifying glass, in the “Visualization” pane turn on Action and select Type “Page navigation” & set Destination for your original page (Overview in this example). I also turn on Tooltip (the only one for this entire page promise!) for this and instruct users to “Click here to turn on tooltips”.
9. Finally, go back to your original page and repeat steps 7 and 8 with the magnifying glass with a check mark to signify tooltips are on. To your end users, this will feel like a button and by keeping visuals on a separate page, you’ll see a minimal impact to performance.
PRO TIP
To have a true button experience, use the “Properties” section of the “General” tab in the “Visualizations” pane to have the visuals align perfectly and be the exact same size.
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!
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!
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
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
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.
/ 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.
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
Making multilingual reports in Power BI requires a lot of different elements. Translations can be added to PBIX files to translate column names, visual titles, etc. but these translations will not impact the data itself. One way to translate the dimensions themselves (star schema introduction) is to have a translation table that contains a display language and value column for each ID of the dimension. In the example below, the translation table would replace the original table and we could apply RLS to the Language column.
Data Model Edits
To make RLS work in this manner, that is to filter down one table and not the entire data model, there are a couple of things that need to be set up. First, you will need a bridge table for every translated table that has a unique list of the ID’s. Second, the RLS filter needs to be added to each of the tables that has a translation. Lastly, the relationships need to be re-established so that the translated tables are only related to their bridge table.
Add RLS Filters
In order to apply RLS dynamically in an embedded Power BI scenario, the locale needs to be passed through the embedded token as an argument within the UPN (dynamic RLS instructions ). Unfortunately, Power BI can only receive one value for each embedded token, so if you need true RLS on the report along with dynamic filtering for the translation, you will need to pass both arguments through the embedded token and separate them later. For example, if the UPN of the user was example@test.com, you can program the embedded token to send the default browser locale send by that user and the UPN of the signed in user in this format: UPN|locale.
It looks a bit goofy, but the pipe delimitator will allow us to separate out the two RLS arguments via the DAX function PATHITEM(). With PATHITEM(), we can tell the RLS filter to either choose what’s before the pipe (the UPN in this case) or what’s after (the locale) to pass through for the role filter. The filter for the example above would be:
[Language] = PATHITEM(USERPRINCIPALNAME(),2)
Once you have applied this filter to all of the tables with the Language field, you’ll need to make some adjustments to your data model.
Reconnect Relationships
Per the diagram below, you will need to connect the bridge table into your existing data model. Remember, the ONLY relationship your translation table should have would be to the bridge table. Every other fact table can join to the bridge table. The joins are as follows – Translation Table has ONE bidirectional join as * to 1 to the Bridge Table, Bridge table joins to every fact table single direction with Bridge Table filtering the fact tables in a 1 to * relationship.
NOTE: do not use CROSSFILTER() in measures with tables that have RLS applied. They will break in the online Power BI service.
And that’s all there is to it! Hopefully this saves you time when building a multilingual, embedded report. Happy coding!
Thank you to everyone who made it out to PASS Data Community Summit! I have greatly enjoyed meeting so many fellow data nerds and have learned so much from all of you. Speaking in person is always a joy, but this crowd was one of the best. Thank you for laughing with me and asking great engaging questions. If you haven’t already, please connect with me on LinkedIN and reach out with any additional questions or fun stories as you begin to scale your Power BI! If you have seen this presentation before, please check out the section where we use actual DAX inside a calculated column and measure instead of just text. It was an adventure to update the code the day of presenting, but well worth it to add a little more flair to the presentation.
As promised, below is the code used in my presentation as well as a link to my GitHub with the presentation and zip file to run the code on your own laptop. Happy coding everyone!
Abstract
Tune in to learn how to programmatically add columns and measures to Power BI data models using TOM, XMLA, and C#! It is rare to find a Power BI developer who has a background in C#, but C# and other programming languages offer a lot of automation and scalability that is lacking in Power BI development. The XMLA endpoint is a powerful tool available in the online Power BI service that allows report developers to connect to their data model and adjust a variety of entities outside the Power BI Desktop application. For example, the XMLA endpoints can be used within a pipeline triggered by an application to update a Power BI model schema. This allows end users to create custom UDFs (user defined fields) on the fly and delete them. Similarly, developers can create and use translations for customer specific column renames without worrying about breaking visuals and complicated data models.
using System;
using Microsoft.AnalysisServices.Tabular;
namespace TOM_Testing_Live
{
internal class Program
{
static void Main(string[] args)
{
//-------------- Connect to PBI Premium Workspace ---------------- //
// create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/Happy%20Coding";
string connectString = $"DataSource={workspaceConnection};";
// connect to the Power BI workspace referenced in connect string
Server server = new Server();
server.Connect(connectString);
//---------------- List out current state before we make changes -------------------//
// 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["5d161e6b-697a-49b6-a3e6-7d19b940a8fd"].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"];
// Specify a single table in the dataset (our measure table)
Table table_measures = model.Tables["_Measures"];
// List out the columns in the product table
foreach (Column column in table_product.Columns)
{
Console.WriteLine($"Columns: {column.Name}");
}
// List out the measures in the product table
foreach (Measure productmeasures in table_product.Measures)
{
Console.WriteLine($"Measures: {productmeasures.Name}");
}
// List of translations on the model
foreach (Culture culture in model.Cultures)
{
Console.WriteLine($"Existing Culture: {culture.Name}");
}
// 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
if (table_product.Hierarchies.ContainsName("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} Lineage Tag: {level_categoryhierarchy.LineageTag} from {level_categoryhierarchy.Column.Name}");
}
}
else
{
Console.WriteLine($"No Category Hierarchy");
}
//----------------Deleting columns and measures ----------------//
/*
// Deleting a column if it exists
if (table_product.Columns.ContainsName("Testing"))
//this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Column Exists");
table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
Console.WriteLine($"Column Deleted");
}
else
{
Console.WriteLine($"Column Does Not Exist");
}
// Deleting a measure if it exists
if (table_measures.Measures.ContainsName("Test Measure"))
//this looks to see if there is a measure already named "Test Measure"
{
Console.WriteLine($"Measure Exists");
table_measures.Measures.Remove("Test Measure"); //if the measure exists, this will remove it
Console.WriteLine($"Measure Deleted");
}
else
{
Console.WriteLine($"Measure Does Not Exist");
}
*/
//--------------- Adding columns and measures --------------------//
// Adding our column if it doesn't already exist
if (table_product.Columns.ContainsName("Testing")) //this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Column Exists");
}
else
{
Column column_testing = new DataColumn() //this will add the column
{
Name = "Testing", //name your column for Power BI
DataType = DataType.String, //set the data type
SourceColumn = "Product", //this must match the name of the column your source
LineageTag = "product-testing-column"
};
table_product.Columns.Add(column_testing);
Console.WriteLine($"Column Created!");
}
//Get the partition sources for each table
foreach (DataSource datasource in model.DataSources)
{
Console.WriteLine($"Datasource : {datasource.Name}");
};
// Add a measure if it doesn't already exist in a specified table
if (table_measures.Measures.ContainsName("Test Measure"))
{
Measure measure = table_measures.Measures["Test Measure"];
measure.Expression = "\"Hello there\""; //you can update an existing measure using this script
Console.WriteLine($"Measure Exists");
}
else
{
Measure measure = new Measure()
{
Name = "Test Measure",
Expression = "\"Hello World\"", //you can also use DAX here
LineageTag = "test-measure"
};
table_measures.Measures.Add(measure);
Console.WriteLine($"Measure Added");
}
//--------------- Adding columns and measures with DAX functions! --------------------//
// Adding our column if it doesn't already exist
if (table_product.Columns.ContainsName("List Price w Tax")) //this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Calculated Column Exists");
}
else
{
Column column_testing = new CalculatedColumn() //this will add the column
{
Name = "List Price w Tax", //name your column for Power BI
DataType = DataType.Decimal, //set the data type
Expression = "'Product'[List Price] * 1.05", //this is the DAX used to create your new column!
LineageTag = "testing_w_dax-column"
};
table_product.Columns.Add(column_testing);
Console.WriteLine($"Calculated Column Created!");
}
// Add a measure if it doesn't already exist
if (table_measures.Measures.ContainsName("DAX Measure Example"))
{
Measure measure = table_measures.Measures["DAX Measure Example"];
measure.Expression = "SUM(Sales[Total Product Cost])+100"; //you can update an existing measure using this script
Console.WriteLine($"DAX Measure Exists");
}
else
{
Measure measure = new Measure()
{
Name = "DAX Measure Example",
Expression = "SUM(Sales[Total Product Cost])",
LineageTag = "test-measure-with-dax"
};
table_measures.Measures.Add(measure);
Console.WriteLine($"DAX Measure Added");
}
//------------------- Manipulating Hierarchies ---------------------//
//Editing an existing hierarchy originally called Category Hierarchy
//Once you rename the hierarchy, you'll have to adjust this code to the renamed Hierarchy
Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy Rename"];
{
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("ca792793-d3c3-4b5d-9bee-2b46c01833bb");
Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("4304e645-bb8f-4d7e-b25c-f629be2110d8");
{
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"),
LineageTag = "hierarchy-model-column"
};
hierarchy_category.Levels.Add(level_Model);
Console.WriteLine($"Hierarchy Level Added");
}
//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",
LineageTag = "new-hierarchy-lineage-tag"
};
table_product.Hierarchies.Add(hiearchy_new);
Console.WriteLine($"Hierarchy Added");
//Creating levels to add to the new hierarchy
Level level_one = new Level()
{
Name = "Model",
Ordinal = 0,
Column = table_product.Columns.Find("Model"),
LineageTag = "product-model-column"
};
Level level_two = new Level()
{
Name = "Product",
Ordinal = 1,
Column = table_product.Columns.Find("Product"),
LineageTag = "product-product-column"
};
hiearchy_new.Levels.Add(level_one);
hiearchy_new.Levels.Add(level_two);
Console.WriteLine($"Levels added to new hiearchy");
};
//-------------------------- Translations ------------------------------//
//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("SKU"); //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 = "Cool Stuff"
};
enUsCulture.ObjectTranslations.Add(overwriteTranslation);
}
else
{
ObjectTranslation newTranslation = new ObjectTranslation()
{
Object = dataColumn,
Property = TranslatedProperty.Caption,
Value = "Total Rad"
};
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}");
}
//------------------- List out end state --------------------------------//
// List out the columns in the product table one more time to make sure our column is added
foreach (Column column in table_product.Columns)
{
Console.WriteLine($"Columns: {column.Name}");
}
// List out the measures in the table one more time to make sure our measure is added
foreach (Measure productmeasures in table_measures.Measures)
{
Console.WriteLine($"Measures: {productmeasures.Name}");
}
// List out the hierarchies in the product table
foreach (Hierarchy hierarchy in table_product.Hierarchies)
{
Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag: {hierarchy.LineageTag}");
foreach (Level level_hierarchy in hierarchy.Levels)
{
Console.WriteLine($"Level {level_hierarchy.Ordinal}: {level_hierarchy.Name}, Lineage Tag: {level_hierarchy.LineageTag} from {level_hierarchy.Column.Name}");
}
}
//-------------- Refresh our version of the data model then push/save changes back to the model in the PBI service -------------//
table_product.RequestRefresh(RefreshType.Full);
table_measures.RequestRefresh(RefreshType.Full);
model.RequestRefresh(RefreshType.Full);
model.SaveChanges();
//make sure this is the last line! Note, this line will not work unless your dataset has proper data source credentials connected in the service
Console.WriteLine($"Script Complete!");
}
}
}
Thank you to everyone who came out and saw me present at the SQL Saturday in Baton Rogue! It was a blast to see smiling faces and not be the only one laughing at my jokes haha. HUGE thank you to the team of volunteers who made this event possible and to the sponsors for enabling such an amazing event.
My presentation of Power BI Meets Programmability had 58 people in attendance, the largest crowd of the whole weekend! Out of that came many great conversations about the potential shift in IT teams from separating reporting teams from application development to playing to each other’s strengths to deliver sustainable and scalable reporting to the end-users of your application. As promised, below is all the code used in the session. Thank you again to everyone who attended, and happy coding!
using System;
using Microsoft.AnalysisServices.Tabular;
namespace TOM_Testing_Live
{
internal class Program
{
static void Main(string[] args)
{
//-------------- Connect to PBI Premium Workspace ---------------- //
// create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/Happy%20Coding";
string connectString = $"DataSource={workspaceConnection};";
// connect to the Power BI workspace referenced in connect string
Server server = new Server();
server.Connect(connectString);
//---------------- List out current state before we make changes -------------------//
// 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["5d161e6b-697a-49b6-a3e6-7d19b940a8fd"].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}");
}
// List out the measures in the product table
foreach (Measure productmeasures in table_product.Measures)
{
Console.WriteLine($"Measures: {productmeasures.Name}");
}
// List of translations on the model
foreach (Culture culture in model.Cultures)
{
Console.WriteLine($"Existing Culture: {culture.Name}");
}
// 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
if (table_product.Hierarchies.ContainsName("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} Lineage Tag: {level_categoryhierarchy.LineageTag} from {level_categoryhierarchy.Column.Name}");
}
}
else
{
Console.WriteLine($"No Category Hierarchy");
}
//----------------Deleting columns and measures ----------------//
/*
// Deleting a column if it exists
if (table_product.Columns.ContainsName("Testing"))
//this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Column Exists");
table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
Console.WriteLine($"Column Deleted");
}
else
{
Console.WriteLine($"Column Does Not Exist");
}
// Deleting a measure if it exists
if (table_product.Measures.ContainsName("Test Measure"))
//this looks to see if there is a measure already named "Test Measure"
{
Console.WriteLine($"Measure Exists");
table_product.Measures.Remove("Test Measure"); //if the measure exists, this will remove it
Console.WriteLine($"Measure Deleted");
}
else
{
Console.WriteLine($"Measure Does Not Exist");
}
*/
//--------------- Adding columns and measures --------------------//
// Adding our column if it doesn't already exist
if (table_product.Columns.ContainsName("Testing")) //this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Column Exists");
//table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
//Console.WriteLine($"Column Deleted");
// Column column_testing = new DataColumn() //this will add back the deleted column
//{
// Name = "Testing",
// DataType = DataType.String,
// SourceColumn = "Product"
// };
// table_product.Columns.Add(column_testing);
// Console.WriteLine($"Column Re-created!");
}
else
{
Column column_testing = new DataColumn() //this will add the column
{
Name = "Testing", //name your column for Power BI
DataType = DataType.String, //set the data type
SourceColumn = "Product" //this must match the name of the column your source
};
table_product.Columns.Add(column_testing);
Console.WriteLine($"Column Created!");
}
//Get the partition sources for each table
foreach (DataSource datasource in model.DataSources)
{
Console.WriteLine($"Datasource : {datasource.Name}");
};
// Add a measure if it doesn't already exist in a specified table called product
if (table_product.Measures.ContainsName("Test Measure"))
{
Measure measure = table_product.Measures["Test Measure"];
measure.Expression = "\"Hello there\""; //you can update an existing measure using this script
Console.WriteLine($"Measure Exists");
}
else
{
Measure measure = new Measure()
{
Name = "Test Measure",
Expression = "\"Hello World\"" //you can also use DAX here
};
table_product.Measures.Add(measure);
Console.WriteLine($"Measure Added");
}
//------------------- Manipulating Hierarchies ---------------------//
//Editing an existing hierarchy originally called Category Hierarchy
Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy Rename"];
{
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("ca792793-d3c3-4b5d-9bee-2b46c01833bb");
Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("4304e645-bb8f-4d7e-b25c-f629be2110d8");
{
level_Category.Name = "Category";
level_Category.Ordinal = 0;
level_Subcategory.Name = "Subcategory";
level_Subcategory.Ordinal = 1;
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");
}
//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");
//Creating levels to add to the new hierarchy
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");
};
//-------------------------- Translations ------------------------------//
//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("SKU"); //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 = "Cool Stuff"
};
enUsCulture.ObjectTranslations.Add(overwriteTranslation);
}
else
{
ObjectTranslation newTranslation = new ObjectTranslation()
{
Object = dataColumn,
Property = TranslatedProperty.Caption,
Value = "Total Rad"
};
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}");
}
//------------------- List out end state --------------------------------//
// List out the columns in the product table one more time to make sure our column is added
foreach (Column column in table_product.Columns)
{
Console.WriteLine($"Columns: {column.Name}");
}
// List out the measures in the product table one more time to make sure our measure is added
foreach (Measure productmeasures in table_product.Measures)
{
Console.WriteLine($"Measures: {productmeasures.Name}");
}
// List out the hierarchies in the product table
foreach (Hierarchy hierarchy in table_product.Hierarchies)
{
Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag: {hierarchy.LineageTag}");
foreach (Level level_hierarchy in hierarchy.Levels)
{
Console.WriteLine($"Level {level_hierarchy.Ordinal}: {level_hierarchy.Name}, Lineage Tag: {level_hierarchy.LineageTag} from {level_hierarchy.Column.Name}");
}
}
//-------------- Refresh our version of the data model then push/save changes back to the model in the PBI service -------------//
table_product.RequestRefresh(RefreshType.Full);
model.RequestRefresh(RefreshType.Full);
model.SaveChanges();
//make sure this is the last line! Note, this line will not work unless your dataset has proper data source credentials connected in the service
Console.WriteLine($"Script Complete!");
}
}
}
Data on Wheels - Steve & Kristyna Hughes 
