Power BI Cruise Recap – Deep Data Dives

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

• https://learn.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions#tmdl-preview
• https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-how-to?view=asallproducts-allversions
• https://dax.tips/2020/07/30/automatically-create-measures-in-power-bi-using-vs-code/

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
  • https://learn.microsoft.com/en-us/analysis-services/tmdl/tmdl-overview?view=asallproducts-allversions#default-properties-by-object-type

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
  • https://github.com/Financial-Times/chart-doctor/blob/main/visual-vocabulary/Visual-vocabulary-en.pdf
• / 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?
  • https://learn.microsoft.com/en-us/dax/userelationship-function-dax
  • 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

Excel Tip #30: Excel Services Visual Limitations – Displaying Images

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Introducing Brian Wright – Guest Blogger

Today, I am happy to announce that Brian will be joining DataOnWheels as a guest blogger. I have worked with Brian over the past couple of years and his Excel visualization skills are great. I look forward to his contributions to the Excel Tips series and other BI related topics. Thanks Brian.

Hello Data on Wheels Readers! Let’s start this blog post by letting you know a few things about myself. First, I am not a professional writer, blogger, or ever social guru, but I am passionate about what I do. I love data visualization. Watching boring data come to life in a visual report or dashboard is my “thing”. Secondly, when things don’t work the way I think they should, I become obsessed in finding out a way around it.

Images Are Not Displayed in Excel Services

That is what leads us to this blog post today. In the limited environment I work within, Excel Services is used quite often in our BI suite of tools. When I realized that the ever so important images I was adding in my Excel workbooks would not show on Excel Services, my obsession kicked in.

Here is the trick or hack. (Using the word hack makes me look much cooler in my kid’s eyes). Wherever you want your picture within your workbook, simply add a chart. Yes, you read correct, simply add a chart.

Using Charts to Display Images

The trick here is not to link the chart to any type of data at all. Just leave it blank. Right Click on the blank chart and navigate to “Format Chart Area”. Navigate to the fill area and select “Pattern or Texture Fill”.

Next, click on the File Button and select your image. Your image will now show as a background image in your chart. Save and then voila!

Once Excel Services displays your workbook, you will be pleasantly surprised to see your image right where you want it!

Boston BI User Group Meeting–Dashboard Design with Microsoft: Power BI vs Datazen (10/13/15)

Thanks for joining Anthony Martin (@SQLMartini) and I at the Boston BI User Group Meeting in October. During the session, we demo’d and built dashboards in Power BI Desktop and Datazen Publisher.

Power BI

www.powerbi.com

Couple of thoughts from our demo:

• Power BI is a way to get data, model data, and visualize it
• Power BI Desktop allows you to work with data on your PC
• Power BI is experiencing a lot of change – for example over 40 changes were applied in September 2015
• Power BI has an open API that allows you to customize the experience

Datazen

www.datazen.com

Couple of thoughts from our demo:

• Design first scenario – make it look good, then shape data to match
• Datazen publisher allows us to create dashboard for many different profiles
• Datazen handles custom shapes

Additional Training from Pragmatic Works

• Free Power BI Webinars
  • Creating Solutions with the New Power BI Desktop
  • Where Does Power BI Fit in Your Data Architecture-
  • Automating Power BI Creations
  • Introduction to the Power BI Designer
  • Power BI- Data Refresh and SSAS On-Premises Connectivity
  • Power Pivot 102- Advanced Power Pivot Modeling
  • Powering Up HDInsight with Power BI
• Free Datazen Webinars
  • Intro to Datazen- Dashboard Design and Development
  • Intro to Datazen- Setup Your Datazen Environment

Questions from the Session

Can you use links in Datazen to support drillthrough?

Yes. You can find more information here: Drill-throughs to Other Dashboards or Custom URLs.

Power BI API Development

You have the ability customize Power BI. Check out the contest winners to get some ideas of what is possible.

Details about the solutions can be found on the Power BI blog: http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest/tab/entries.

You can find more about custom visuals here: https://powerbi.microsoft.com/en-us/custom-visuals.

Thanks again for joining us.

Excel Tip #29: Forcing Slicers to Filter Each Other when Using CUBE Functions

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Scenario

You have went to all the trouble to build out a good set of slicers which allow you to “drill” down to details based on selections. In my example, I have created a revenue distribution table using cube formulas such as:

=CUBEVALUE(“ThisWorkbookDataModel”,$B6, Slicer_Date, Slicer_RestaurantName, Slicer_Seat_Number, Slicer_TableNumber)

Each cell with data references all the slicers. When working with pivot tables or pivot charts, the slicers will hide values that have no matching reference. However, since we are using cube formulas the slicers have no ability to cross reference. For example, when I select a date and a table, I expect to see my seat list reduce in size, but it does not. All of my slicers are set up to hide options when data is available. There are two examples below. In the first, you can see that the seats are not filtered. However, this may be expected. In the second example, we filter a seat which should cause the tables to hide values and it does not work as expected either.

As you can see in the second example, we are able to select a seat that is either not related to the selected table or has no data on that date. Neither of these scenarios is user friendly and does not direct our users to see where the data matches.

Solving the Problem with a “Hidden” Pivot Table

To solve this issue, we are going to use a hidden pivot table. In most cases we would add this to a separate worksheet and then hide the sheet from the users. For sake of our example, I am going to put the pivot table in plain sight for the examples.

Step 1: Add a Pivot Table with the Same Connection as the Slicers

In order for this to work, you need to add a pivot table using the same connection you used with the slicers. The value you use in the pivot table, should only be “empty” or have no matches when that is the expected result. You want to make sure that you do not unintentionally filter out slicers when data exists. In my example, I will use the Total Ticket Amount as the value. That will cover my scenario. In most cases, I recommend looking for a count type value that will always have data if there is a potential match of any kind.

Step 2: Connect the Slicers to the Pivot Table

Using the Apply Filters button on the Pivot Table ribbon, you need to select all the slicers you want to interact with each other.

Once these changes are applied, you will see how my data changed.

Now, let’s test this for real. We will keep the date and table, but now we will see that the other slicers are now filtered to match the data that is available.

As you can see, the solution is fairly simple, but not intuitive. You will be able to create more creative dashboards with this technique. Keep in mind this issue is primarily a problem when using cube formulas in your Excel dashboard.

Until next time…

Excel BI Tip #28: Using member expressions in the CUBEVALUE Function

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

CUBEVALUE and CUBEMEMBER

If you have not looked at the cube functions in Excel check out two of my previous posts: Excel BI Tip #18: Using Cube Functions to Break Out of Pivot Tables and Excel BI Tip #27: CUBESET and CUBESETCOUNT Functions. Cube functions allow us to apply MDX functionality in Excel beyond pivot tables and charts. In this post, I will show how you can expand the CUBEVALUE function by directly using member expressions or CUBEMEMBER functions in the syntax. Tip #18 demonstrates how to use cells in Excel.

Typically, you would use these functions separately. For example, CUBEMEMBER is used in column and row headers in a typical solution. Because of this CUBEVALUE usually refers to a series of cells and slicers.

CUBEMEMBER used as a column header:

=CUBEMEMBER(“AdvWorks”,”[Measures].[Customer Count]”)

CUBEVALUE referring to this member:

=CUBEVALUE(“AdvWorks”,$D8,F$7,Slicer_Commute_Distance)

The snapshot below shows how it looks in Excel when the cell with the CUBEVALUE function is selected:

Using Member Expressions and CUBEMEMBER

The method illustrated above is how the cells are made when a pivot table has been converted to formulas. However, if you look closely the CUBEVALUE function accepts CUBEMEMBER values. The next few examples show how you can use members directly in the name to get more functionality.

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,CUBEMEMBER(“AdvWorks”,”[Customer].[Gender].[Female]”))

 

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,”[Customer].[Gender].[Female]”)

 

You can use any MDX expression that returns a member as one of the expressions in the function. This includes functions like STRTOMEMBER which allows you to create dynamic functions to get current date. Using member expressions allow for even more customization without dependencies on reference columns.

Adding a Slicer

Finally, you can add a slicer reference to the CUBEVALUE function.

=CUBEVALUE(“AdvWorks”, “[Measures].[Customer Count]”,”[Customer].[Gender].[Female]”, Slicer_State_Province)