Author Archives: Alexander Korn

My Power BI Toolbox: 80+ Tabular Editor Macros to Automate Data Model Development

When it comes to streamlining your Power BI data modeling, the value of automation cannot be overstated. One of the most powerful tools available to achieve this automation is Tabular Editor, where you can write C# scripts to optimize and automate repetitive tasks. In this blog post, I’ll introduce you to my Power BI Toolbox — an extension of my original Pimp script — and share how it helps bring efficiency and best practices to Power BI data modeling.

Tabular Editor Scripts: The Key to Automation

Tabular Editor Scripts, also referred to as macros, C# scripts, or custom actions, are essential tools for automating actions within Tabular Editor. Tabular Editor, an invaluable tool for Power BI professionals, offers the ability to write custom scripts to save time and ensure consistency in data models. With C# scripting capabilities, you can automate a huge collection of tasks like creating measures, setting properties, and sooo much more.

Over the years, I’ve developed and wide range of these scripts. My Power BI Toolbox is after my PIMP script the next evolution in this journey, containing a suite of over 80 Tabular Editor scripts, each designed to simplify various aspects of Power BI data modeling. If you want to know more about why this stuff is important for Tabular models check out the following video with Reid Havens. https://www.youtube.com/watch?v=-9YaxArn3TM

What’s in the Toolbox?

My Power BI Toolbox is a collection that includes:

  • Many of the Official Tabular Editor repository macros (excluding those exclusive to TE3)
  • Bernat’s macros, You will definitely find hidden gems there.
  • My own macros, which build upon the lessons learned from my earlier PIMP script

More Details on My Favorite Macros

Here are some of my favorite macros that I’ve developed to improve efficiency and automation in Power BI data modeling:

  • Calendar Table as Calculation Group: This macro helps you create a calendar table as a calculation group and mark it as the calendar table in your data model. It’s a quick way to ensure that your time intelligence functions are referencing the correct table.
  • Time Intelligence Calculation Group: This script automatically detects the calendar table and creates a calculation group for common time intelligence measures like Year-to-Date, Quarter-to-Date, Month-to-Date, Previous Year, and more. This reduces manual work and helps ensure consistency in your model.If you have not marked your calendar table as calendar table it will ask you for it.
  • Units Calculation Group: Creates a calculation group for units without splitting text or ratio measures, making it easier to maintain a clean, efficient model structure.
  • Empty Measure Table Creation: This macro allows you to create an empty measure table, which is useful for organizing measures and maintaining a clean data model structure. The script needs to be run in two steps, first creation, than save, than hiding the column.
  • Explicit Measure Creation for Selected Columns: This macro lets you create explicit measures for selected columns and move them into a designated display folder. Additionally, it prompts you with the option to move these measures into a different table, such as an empty measure table, ensuring your data model is organized.
  • Previous Year, Delta PY, and Delta PY % Measure Creation: Automatically generates explicit measures for all selected measures with the variation previous year, delta previous year, and delta previous year percentage. The script is smart enough to recognize the marked calendar table and link these measures appropriately, saving you a lot of repetitive work.
  • Macros Containing Metadata Documentation This macro inspired by Martyn Booth is design to automatically document the model. The macro adds four new calculated tables need to be added, saved, and then hidden as part of the standard workflow. These tables can then be used to effectively document the data model, providing valuable metadata insights. The four calculated tables are: info.view.Tables, info.view.Columns, info.view.Measures, and info.view.Relationships. This approach essentially replaces the need for additional tools like VertiPaq Analyzer or Bravo for Power BI, to a considerable extent. One limitation is the absence of column size information, which would be a useful enhancement, but the benefit of automatic updates makes this a powerful feature for keeping documentation current without extra manual work. More info here: https://www.notjustaprettydashboard.com/auto-documentation-report-using-info-view-dax-functions/
  • Combine Time Intelligence with Value Parameter: This macro, inspired by Mark Endicott, extends my time intelligence template by adding a dynamic value parameter. The number of years is now made dynamic, allowing for more flexibility in time-based analysis. The next step is to make the period selection fully dynamic, enabling switching between different time frames such as year, quarter, month, and day.

These scripts are targeted at best practices in Power BI data modeling, enabling you to standardize and streamline your workflows in a consistent, efficient manner.

No Admin Rights? No Problem

No admin rights? No problem. Just make sure you’re running the latest version of Tabular Editor Portable. This version allows you to use all the powerful features of Tabular Editor without needing to install anything, making it perfect for environments where you lack administrative privileges.

How to Install the Power BI Toolbox for Tabular Editor

Installing the Power BI Toolbox is straightforward. You can use the Installation Script available here: Installation Script. Or as an alternative you can also just copy paste the MacroActions.json file into your AppData\Local\TabularEditor directories. Just paste into your file explorere “%AppData%” and you are already almost there. 

Here is a video of how to install it, if manual is not what you prefer: https://www.linkedin.com/posts/alexanderkorn_hicocando-actionablereporting-tabulareditor-activity-7250519843966181377-sfdd?utm_source=share&utm_medium=member_desktop

The installation script takes care of the following steps:

  1. Backup the MacroActions.json file if it exists in your AppData\Local\TabularEditor or TabularEditor3 directories.
  2. Add a fresh MacroActions.json file from my GitHub repository if one does not already exist.
  3. Append my DataModelToolbox.txt file to the current MacroActions.json file if one exists. Please note, if you already have some of my macros, this script does duplicate all of them.

The MacroActions.json file is where all the magic happens, containing the macros that Tabular Editor will use to automate your Power BI tasks. You can check it out here: MacroActions.json File.

How to Execute C# Scripts in Tabular Editor 2

To execute C# scripts in Tabular Editor 2, follow these simple steps:

  • Connect to Open Power BI Desktop: Start by connecting Tabular Editor to your open Power BI Desktop instance to gain access to the data model.
  • Right-Click Options: You can execute scripts by right-clicking on a table, column, data model, or measure within the model. Depending on the certain Macro it will be visible within the context menu where you can select and run the script.
  • Navigate to the C# Section: Alternatively, you can directly navigate to the C# scripting section within Tabular Editor, where you can write and execute your scripts.

Shortcuts for Macros

In Tabular Editor 3, you can even assign shortcuts to these actions, giving you more flexibility and making repetitive tasks even faster. This is especially useful when working with large and complex data models where efficiency matters most. 

Here is a video of this in action: https://www.linkedin.com/posts/alexanderkorn_do-you-also-have-a-button-to-implement-power-activity-7252416004079677443-0HHC?utm_source=share&utm_medium=member_desktop

You can either set the shortcuts for the macros under Tools > Preferences > Keyboard or alternatively copy and paste my UiPreferences.json file into your directory AppData\Local\TabularEditor3. You can find the UiPreferences.json file here: UiPreferences.json.

I am using the following shortcuts for my macros:

Macro ID Range Shortcut Combination
1 to 9 CTRL + SHIFT + Number (1 to 9)
10 to 19 CTRL + ALT + Number (1 to 9)
20 to 29 CTRL + ALT + SHIFT + Number (1 to 9)

I need to give for the keyboard shortcut and Stream Deck idea full credit to Bernat Agullo Rosello. He gave me the whole idea. I wasn’t aware of Stream Deck till he pointed me that way. While he had the idea, I went ahead and made it real. I am now using a Stream Deck with custom icons for these shortcuts, which allows me to apply data model changes with just the press of a button. This setup significantly enhances my productivity by making these powerful macros available at my fingertips. With the Stream Deck, I can visually organize the most frequently used macros, assign dedicated buttons to each, and execute them instantly. This workflow improvement not only saves time but also makes navigating complex models more intuitive, as I can easily trigger common actions without needing to remember numerous key combinations or navigate through menus.

Sorting the IDs

All Macros have an ID, those can be consequential but don’t need to be. The sorting in Tabular Editor 3 is by default exactly like the MacroActions.json file is sorted. That being said, if you move macros around in the json file, than the IDs do not get automatically adjusted. At the time of writing you will see that actually my file skips a few IDs and is not perfectly sorted. Andreas Nordgaard provided for us a free Python script which does the job.

Save the python script in the same location as the Macro file install python, potentially modify the python script to use the correct parent path and you are good to go.

Just be careful if you change your IDs those will than potentially not match your Tabular Editor 3 shortcuts anymore.

    https://github.com/aols0228/Semantic-Models/blob/main/Macros/MacroActions.json/Update%20Id’s%20in%20MacroActionsJSON.py

    Stream Deck Profile

    In case you also have Stream Deck and want to overcome the hustle to set it up here is my Stream Deck profile, which should have all the icons. Just make sure your shortcuts in TE3 align with the profile. https://github.com/KornAlexander/PBI-Tools/blob/main/Data%20Model%20Toolbox/Tabular%20Editor.streamDeckProfile

    Why Automate?

    The Power BI Toolbox is more than just a collection of scripts; it’s about bringing best practices to every model you create. It’s about removing friction, increasing productivity, and reducing the chance of human error when performing repetitive tasks. Automation lets you focus on what matters most: making data-driven decisions that impact your business.

    Get Started Today

    Ready to give your Power BI data modeling a productivity boost? Install the Power BI Toolbox and start automating today! You can access the installation script here and transform how you model your data or alternativly just add it manually.

    Let me know if you have any questions or if you need help getting started. Maybe do you have a killer script I need to add. Let me know. Would be very happy to receive it. Happy modeling! 🙂

    Video Demo

    Spot Poor Power BI Reports: No4. No “EMPTY MEASURE TABLE” 

    When building Power BI reports, deciding where to place your measures—whether in a dedicated measure table or directly in the fact table—can impact both usability and maintainability. Here’s a look at why creating a separate measure table often works best and why it’s becoming a popular approach for Power BI developers and report consumers alike. I call the approach of a dedicated measure table as “EMPTY MEASURE TABLE” because it is just a container, no actual data is in there, except maybe the relationships but that’s a minor detail.

    Advantages of a Dedicated Measure Table

    1. Clear Structure for Reports Under Development: Fact tables are often “work in progress” —especially in development or test environments — where new measures or transformations may still be in the pipeline. A measure table provides a consistent location for measures even if the fact table is incomplete.
    2. Ease of Development: From a developer’s perspective, having all measures in a single, separate table makes it easy to find and work with them. It avoids digging through the fact table, especially when it’s not fully denormalized.
    3. Attributes in Fact Table are unavoidable: Normally attributes in fact tables like an invoice number should be ideally avoided. But as we all know business request are a priority and this can’t be always avoided. As a result the fact table will not move to the top with the calculator icon. And if that is the case Measure Table for sure it is. A separate invoice number dimension table is also not optimal.
    4. Easier Tracking of Incomplete Work: Developers can quickly see if a fact table still needs further adjustments without having to go through measure definitions, as all measures are in their own place.
    5. Non-Disruptive Maintenance: When tables are deleted, measures in a separate measure table remain untouched, avoiding accidental data model breakages due to table deletions.
    6. Improved User Experience: Users may not always understand the difference between fact and dimension tables, and a dedicated measure table avoids confusing them with technical table names. This is especially helpful when users need quick access to measures such as variance or trend measures across multiple tables. Users don’t have to dig through multiple tables to find what they need.
    7. Consistent Naming Conventions: A measure table supports streamlined naming conventions. While dimensions and facts are often tagged in table names, this isn’t necessary with a measure table, which can be kept hidden or labeled more intuitively, contributing to a cleaner, more navigable model.

    Encounter the disadvantage of Excel functionality:

    Is the data model used in excel a lot the empty measure table approach should very carefully considered and maybe thrown over board.

    1. Drillthrough Flexibility: Yes the big disadvantage of missing the drillthrough needs to be addressed with additional work with detailrowexpression. Maybe we could set it on the table level instead of on the measure level. For more details on this: https://www.sqlbi.com/articles/controlling-drillthrough-in-excel-pivottables-connected-to-power-bi-or-analysis-services/
    2. Missing Relationships: In Excel normally the relationships are automatically visible. That means no wrong relationship can be used. With “Empty measure tables” manually setting up fake relationships is needed. Obviously this can’t be done as exact, since different measures might have different relationships.

    Naming Convention

    Did you know that you can’t use “Measures” as name for your empty measure table? The best next alternative is “Measure” – Easy fix.

    Organization in Display folders

    Obviously organizing the measures in folders and subfolders is needed, but this is unrelated to the fact which approach should be chosen. Organizing that way can be done in both worlds.

    But how do I implement it?

    Here is a super easy two step process in Tabular Editor 2. Just paste it into C# Script and Execute the two scripts separately.

    // ATTENTION FOR TE2 Users: Script needs modification AND needs to be run in 3 steps
    // First Step: Add Table
        var table = Model.AddCalculatedTable("Measure", "{0}"); 
    // Second Step: JUST FOR TE2 Save Data Model Changes
    // Third Step: Hides the column, uncomment the next two lines and execute it separately to the previous creation
        var table = Model.Tables["Measure"]; 
        table.Columns[0].IsHidden = true;

    Final Thoughts

    Using a separate measure table provides a cleaner, more efficient Power BI model that’s easier for both developers and end-users to navigate. This approach aligns with best practices in data model design, creating a well-organized, scalable, and maintainable report structure.

    And once again if Excel usage is a priority, the approach should be carefully considered and maybe revert back to the fact table approach. But than push hard to have it fully denormalized / all columns hidden.

    Spot Poor Power BI Reports: No3. No Units Calculation Group

    When working with large datasets in Power BI, displaying big numbers—especially those over a million—can become a challenge. Lengthy figures not only clutter your visuals but can also confuse your audience. To enhance readability and comprehension, it’s essential to format these numbers into thousands, millions, or even billions. While Power BI offers several methods to achieve this, not all are optimal.

    In this article, we’ll explore the various approaches to handling large numbers and show why implementing a “Units Calculation Group” is often the best solution.

    Common Approaches to Displaying Large Numbers

    There are several strategies you might consider when formatting big numbers in Power BI:

    1. Automatic Formatting by Visuals
    2. Fixing the Visual to a Specific Unit
    3. Using a Helper Table in Measures
    4. Implementing a Units Calculation Group

    1. Automatic Formatting by Visuals

    Letting the visual handle the formatting automatically seems convenient. However, Power BI’s automatic unit selection can sometimes be suboptimal. You might find your visuals displaying “0” or “1 million,” which lacks the necessary detail, especially without decimal places.

    2. Fixing the Visual to a Specific Unit

    Manually setting the unit (e.g., thousands, millions) in the visual’s format settings can work, but it locks you into one unit. This rigidity doesn’t accommodate data that spans multiple scales, limiting the flexibility of your report.

    3. Using a Helper Table in Measures

    Adding a helper table and integrating it into your measures offers more control but increases complexity. This method can clutter your model and make maintenance more challenging, especially as your data evolves.

    Why Implement a Units Calculation Group?

    Given the limitations of the previous methods, implementing a Units Calculation Group is often the most effective solution. This approach provides flexibility and precision, allowing you to display numbers in the appropriate units with the desired level of detail.

    Benefits of Using a Units Calculation Group in Combination with Dynamic Format String

    • Dynamic Unit Selection: Users can switch between units (e.g., thousands, millions) as needed.
    • Enhanced Readability: Proper formatting with decimal places improves data comprehension.
    • Consistency: All relevant measures are automatically included.

    Tips for Implementing a Units Calculation Group

    To ensure your Units Calculation Group works effectively, consider the following tips:

    1. Default Measure Display: The option for the actual measure without any selection (SELECTEDMEASURE()) isn’t necessarily needed. If no unit is selected, the actual measure will display by default.
    2. Exclude Text Values: Use ISNUMBER(SELECTEDMEASURE()) to exclude text measures from the calculation group, preventing errors.
    3. Exclude Percentage or Ratio Measures: Apply a condition like NOT(CONTAINSSTRING(SELECTEDMEASURENAME(), “%”)) to exclude percentage or ratio measures that shouldn’t be scaled.
    4. Use Dynamic Formatting Strings: Implement dynamic formatting to add decimal places for millions but not for thousands or when no unit is selected. This enhances precision where needed without cluttering smaller numbers. See below for DAX code.
    5. Display Selected Unit in Titles: Incorporate the selected unit into your visual titles or within the dynamic formatting string. This provides context to users about the scale of the numbers they’re viewing.

    The Magic Sauce for the Units Calculation Group: Example Calculation Item: “Thousand”

    Below is the full definition of the calculation item for “Thousand.” You can adapt this for the “Million” calculation item by adjusting the divisor.

    IF(
        ISNUMBER(SELECTEDMEASURE()),
        IF(
            NOT(
                CONTAINSSTRING(SELECTEDMEASURENAME(), "%") ||
                CONTAINSSTRING(SELECTEDMEASURENAME(), "ratio")
            ),
            DIVIDE(SELECTEDMEASURE(), 1000),
            SELECTEDMEASURE()
        ),
        SELECTEDMEASURE()
    )

    Explanation of the Code

    • ISNUMBER(SELECTEDMEASURE()): Checks if the measure is a number to exclude text values.
    • NOT(CONTAINSSTRING(…)): Excludes measures that contain “%” or “ratio” in their names.
    • DIVIDE(SELECTEDMEASURE(), 1000): Scales the measure down to thousands.
    • SELECTEDMEASURE(): Returns the original measure if conditions are not met.

    The Magic Sauce for Dynamic Format String Based on the Calculation Group

    SWITCH(
        SELECTEDVALUE( UnitCalcGroup[Unit] ),
        "NameOfCalcItemThousand", "0,#",
        "NameOfCalcItemMillion", "#,0.#",
        "#,#"
    )

    Incorporating Units into Visual Titles

    To dynamically display the selected unit in your visual titles, use the following DAX expression and combine it with static text

    SELECTEDVALUE(UnitCalcGroupName[Unit])

    This expression retrieves the currently selected unit from your Units table, allowing you to inform users about the scale directly within the visual.

    Conclusion

    Optimizing the display of large numbers in Power BI reports is crucial for clarity and professionalism. While multiple approaches exist, implementing a Units Calculation Group offers the most flexibility and control. By following the tips outlined above, you can enhance your reports, making them more informative and user-friendly.

    Remember, the goal is to present data in a way that is both accurate and easily digestible. With the right formatting techniques, your Power BI reports will not only look better but also provide greater value to your audience.

    Spot Poor Power BI Reports: No2. The Lonely Fighter

    Okay this is not exactly a poor Power BI report or mistake per se, but might be just one contributing factor for your “poor Power BI report”.

    Have you learned your Power BI skills mostly by yourself via YouTube, reading through the endless Microsoft documentation, or some “secret blog article” like mine? 😉

    Here is a tip for you. Why don’t you just “call” support? I urge you to do so. Have you ever done so? And yes, with the millions of Power BI users, honestly, 99% of the questions have already been asked and answered, and yes we all have our AI helper, but still till today, I believe “calling” support is still a very valid and underestimated option.

    Probably lots of my former colleagues are going to hate me for this tip since they are maybe already flooded with support requests. But it’s their job to help you, not just when your Power BI report crashed and you are super desperate, when it is already too late. Instead, there is also something called “advisory cases.” In short there is no need to be the lonely fighter.

    Did you know that everyone, even with just a Pro license, is entitled to create support tickets as they wish? Honestly, depending on your support entitlement, you might not always receive the exact help you were looking for, but if that’s the case, then voice your concern. I guarantee you! After the case is closed, you have the possibility to enter feedback, and I am sure your feedback is highly valued. Even more important is positive feedback. 😉

    But if you are lucky, you might also talk to some of the most knowledgeable Power BI experts in their domain. I dare to say no Power BI consultant or Power BI developer, not even MVPs, will match their expertise, because only a few people can see that many different environments and can niche down on certain areas as they can.

    Support people at Microsoft are all super nice. Maybe they will give you additional things to collect, but isn’t it okay for them to ask for information from you when you request their help? So please don’t be rude if you think the “information” they asked for is useless or installing the newest Power BI Desktop version is not helping at all. It might indeed just be what they need to proceed with your case.

    And in case all of this is still not helping, obviously you could also ping me via LinkedIn or mail. 😉

    Everything you need to know about Power BI Support:

    Spot Poor Power BI Reports! No1: The Slicer Mess

    This article is part of a series on how to spot a poor Power BI report. There are many pitfalls, which are obvious like 3D Pie Charts, but then there are others which are less obvious. We all have done those mistakes, we actually all of us make some of those still every day, aware or unaware. I want to highlight a few of them in this upcoming series.

    I wrote already about one misconception in the past, so check it out: https://actionablereporting.com/2023/12/21/myths-about-red-green-deficiency-in-visualizations/

    I would like to start with one “Power BI Failure” which is definitely less obvious, heck maybe even up for discussion: Slicer Vs Filterpane

    If you’re familiar with Power BI, you know there are three primary ways to “filter” a report (page):

    1. Filter pane
    2. Visuals
    3. Slicers

    One sign of a poorly designed Power BI report is the excessive use of slicers. Here’s why using option 1 or 2 is in most cases better than option 3.

    1. Increase Information Density Without Losing Clarity

    • Visual Importance: If an element is significant enough to be on the report page as slicer, than it should be a visual like a bar chart. Visuals provide almost the identical functionality as slicers, with the added advantage that visuals convey information while slicers just don’t. The use of space can be the same or at least similar. In case you don’t know multi-select is possible with CTRL.
    • Filter Pane Utilization: In case you want to permanently apply the selection than you still can revert back to the filter pane. This approach not only streamlines the report but also reduces visual clutter.
    • Collapse and Expand Issues: Slicers cannot collapse or expand. Unless you design a slicer pane in your report. That’s definitely something I try to avoid at all costs, because that’s a set up for failure through a bookmark nightmare.

    2. Performance: Faster Reports

    • Impact on Loading Times: Each element on the page needs to be rendered, and visuals must wait for slicers to load as well. This can significantly slow down the report.
    • Optimization Tip: Set slicers to “All.” If nothing is selected. Supposedly that’s faster.

    3. Structure and Simplification

    • Consistency is key for user familiarity: Everyone who has used Power BI in the past at least once, can answer the following question: Where is the filter pane in Power BI? Easy: on the right side of the report. Does the same apply for slicers? Nope, sometimes those are top, left, right or maybe even scattered. No matter which report or company you are going to work with the filter pane is going to be on the right side of the report. Rarely if ever is the filter pane disabled. Did you know you can do that as well?
    • Single Source of Filtering: Totally ommiting is by the way often not an option, because you want to give the users often the possibility to filter more than just one or two attributes, therefore you absolutely need the filter pane. And if you are already using the filter pane than make the single source of filtering. Have you tried to put 20 filters as slicers on your page? Good luck with that. Not saying you should do it with the filter pane. But at least space usage is minimal and structure is super neat.
    • Three Levels of Granularity: The filter pane is super clear on the possibility to filter. There are three levels:
      • Visual
      • Page
      • report –> I try to reside just to this one. Skipping page and visual if possible

    In contrast have you tried to sync slicers across many pages? That can be a nightmare to maintain, which is visible on which page and which is syncing on which page?

    • Order and Size Consistency of filter: The order of filters in the filter pane is super consistent. I tend to number those filters in the filter pane. Each element is uniformly sized and has similar possibilities to filter. I tend to number the filters through with modification of their text. Note that numbering the filters in the filterpane shouldn’t be done if translations are in the game.
    • Search Functionality: Searching values in the filter pane is enabled by default. With slicers this also possible, you need to enable it through the three dots before.

    Exceptions: Where slicers have some small advantage:

    If you haven’t noticed I am not a big fan of slicers, but I want to acknowledge some arguments in favor of slicers.

    1. Hierarchies: When dealing with hierarchical data. Hierarchies in filter pane not possible, but in slicers it is. Edit 17. July 2024 But potential even a bigger performance impact.
    2. Logos: If you use your slicers with small pictures on your page such as logos, I am okay for that. Picture in the filter pane. Not possible.
    3. Unspottable Slicers: When a slicer with a field parameter overlays invisibly the new card visual, it can be the bomb. (e.g. you cards become clickable for different measures how cool is that)
    4. Title Integration: Once again if slicers are not noticeable I am okay too, such as part of a title or other text element you would have on your page either way.
    5. Publish to Web: okay this is definitely an argument for slicers. Filterpane is not available for reports published to the web (To be clear: I mean web not Power BI Service)

    In conclusion, while slicers can be a useful tool in Power BI, for me it is clear: The less slicers –> the better your Power BI. Yes I am also sometimes resorting back to slicers and yes sometimes the combination of all three options is the magic path.

    Increasing information density, having faster reports, and a better structure in my filters is key for me and I hope you take my advice and will think about the (over-)use of slicers in the future twice. 😉

    Edit: 17. July 2024

    Sharing this blog article I got great input from various people. My opinion still stands “it depends” or “both” is the right answer. With me clearly trying to lean towards the filter pane as much as possible. As long as you make an explicit for one or the other than you are good to go.

    Overall the majority was rather opposing my standpoint and the survey also supports this:

    Result of Survey

    Supporting / Supplemental arguments

    However some also supported my point of view such as Armand van Amersfoort performance argument.

    Or also Kerry Kolosko posted a great rule of thumb, which I support 100%

    Opposing Viewpoint

    If you are interested to read a completely opposite viewpoint than check out this great article from Johnny Winter:

    https://greyskullanalytics.com/should-you-hide-the-filter-pane-in-power-bi

    “IBCS Power BI Generator”: Automate your Power BI report development

    DISCLAIMER: You are running the tool at your own risk. The tool is currently deleting and replacing all current report pages, including all visuals. So make sure to have a backup. I continuing to develop the tool as we speak. This is hopefully just a start.

    Manual

    1. Enable PBIP+TMDL Preview Feature in Power BI Desktop
    2. Download and run the script
    3. Select your existing PBIP File (Just the data model needed)
    4. Select your logo, corporate color, value column to analyse, date column…
    5. Done

    Introduction

    In today’s data-driven business environment, the need for clear, comprehensive, and automated reporting is more crucial than ever. The IBCS Power BI Implementer is at the forefront of this revolution, offering a fully automated report creation process that is seamlessly integrated with HICO’s Best Practice Templates. This innovative solution not only streamlines the reporting process but also personalizes it to suit your business’s unique needs and branding requirements.

    What is IBCS Power BI Implementer?

    IBCS Power BI Implementer is an executable tool that enhances the capabilities of Power BI, Microsoft’s interactive data visualization software. This Implementer allows you to automate the entire report creation process, from data model configuration to the final design elements. It’s like having an expert assistant that takes over the tedious tasks, freeing you to focus on analyzing the data and gaining insights.

    Key Features of IBCS Power BI Implementer

    The IBCS Power BI Implementer comes with a wide array of features that cater to different aspects of report creation. Here’s a closer look at what each selection option entails:

    Option1: Date Dimension Calculated Table

    Automating the creation of a calculated table for date dimensions ensures that your reports can effectively handle time-based data, which is essential for trend analysis and time series forecasting.

    Option2: Date Dimension PQ Script

    Power Query (PQ) scripts for date dimensions are vital for processing and transforming date-related data, ensuring consistency across all your reports.

    Option3: Last Refresh Table and Measure

    This option automatically generates a table and measure that keep track of the last time your data was refreshed, providing transparency and ensuring data is up-to-date.

    Option4: Calculation Group – Time Intelligence

    Creating calculation groups for time intelligence simplifies the process of comparative analysis over different time periods, such as year-to-date calculations and period-over-period comparisons.

    Option5: Calculation Group – Units

    This allows for automated conversion and handling of various units within your data, making the reports versatile and adaptable to different measurement systems.

    Option6: Empty Measure Table

    An empty measure table is crucial for organizing custom calculations and measures that you may add to enhance your reports.

    Option7: Sales-Dashboard

    Leverage a template with the custom visual TRUECHART to get a jumpstart on visualizing sales metrics in a way that adheres to IBCS standards.

    Option8: Add Background with company colors including PPTX creation

    Customize the visual aspect of your reports by adding a background with your company’s colors, and even generate a PowerPoint presentation for easy sharing and presentation.

    Option9: Add Company Logo to Design

    Personalize your reports further by embedding your company logo, enhancing brand consistency across all corporate communications.

    Option10: Automatically close and reopen Power BI Report

    For updates that require a restart, this feature ensures the process is seamless, saving time and avoiding manual handling errors.

    Option11: Add Relationship between Fact and Date Table

    Establishing relationships between the fact table and the newly created date dimension table is automated, which is a cornerstone for accurate data modeling in Power BI.

    Option12: Select Column for AC value

    Choose the specific column that represents the Actual (AC) value in your reports. The explicit measures for the column AC, PY, Delta PY are automatically created.

    Integrating with Best Practice Templates

    Integration with templates means that your reports won’t just be automated—they’ll be crafted according to the International Business Communication Standards (IBCS). These standards ensure that your reports are not only efficient but also effective in communicating the right information to the right audience.

    Conclusion

    The IBCS Power BI Implementer exemplifies the next step in the evolution of business intelligence reporting. By automating the entire process and ensuring that each report is customized to your company’s design and data model, it significantly reduces the time and effort required to generate insights. Whether you’re a small business or a large corporation, the IBCS Power BI Implementer could be the tool that transforms your data reporting process into a competitive advantage.

    Here is the executable: https://github.com/KornAlexander/PBI-Tools/blob/main/IBCS%20Power%20BI%20Generator.exe

    Alternative Downloadlink: IBCS Power BI Generator.exe

    ULTIMATE LinkedIn Power BI Report Template: Insights & CV

    You want to jump right into, what the report looks like than go here: https://app.powerbi.com/view?r=eyJrIjoiMDNiOTMyZDgtNjA3ZS00ODJhLTgyYmQtMWUyMTVjM2Y4OTJlIiwidCI6IjllYTUzNDRiLWFiMzAtNGNjYi1hOWFjLThlODU4NWU1ZWZhZSIsImMiOjl9

    In the digital age, the ability to analyze your professional network and present your career accomplishments can significantly impact your success. Two distinct yet interconnected areas where Alexander Korn has excelled are the analytics of LinkedIn engagement and the automation of a curriculum vitae (CV) through LinkedIn data.

    Part I: Analytics of LinkedIn Engagement

    The meticulous LinkedIn statistics report designed by me is more than just a collection of metrics; it’s a blueprint for engagement, growth, and content strategy, inspired by the innovative spirit of the data analytics community.

    A Community-Driven Approach

    The conception of this report was sparked by Jens Becker’s nice presentation at the “German Power BI Expert Circle,” aka “Dashboard feedback” 😉 The presentation of the report and following discussion was the groundwork for my current template. They guided me towards the right resources, proving that the best achievements are often collaborative efforts. 🙂

    One of those resources was a report by Gustav Dudek. Definitely need to give him special recognition since his dashboard served as an absolut main source of inspiration. You will notice that my report for the statistics part is quite similar to his.

    The Template for Success

    Through the shared .pbit file, I am extending an invitation to all professionals to partake in the further development of this report. This tool allows users to map out their LinkedIn data, offering visibility into which content drives engagement and how it influences their network’s growth.

    Part II: Automated CV Based on LinkedIn Data

    In an era where personal branding is paramount, having an automated, data-driven CV can distinguish you in the marketplace. My report goes beyond analytics to include a second page that “automates” the creation of a CV directly from LinkedIn data somewhat.

    By synthesizing LinkedIn data into a dynamic CV, I showcas a novel way of presenting professional stories, where accomplishments and career progress are not just told but shown through the lens of data.

    Manual

    “Just Plug and Play” it is a 3 step process:

    1️⃣ Download All files from 2 places LinkedIn:

    You might need to be creator to see the stats for the posts.
    ! Make sure to change the language before to English, in order to have the exact same column names.

    https://www.linkedin.com/mypreferences/d/categories/privacy And “Get a copy of your data”.

    https://www.linkedin.com/analytics/creator/content/?timeRange=past_2_years

    This is actually a modification of the URL in order to get not just the last year which is accesiable via the UI. Followers before the two years will not be visible per day, instead PowerQuery just adds all of those right before the first day, we get data.

    2️⃣ Create a simple an Excel file manually to give your posts a name and classify them. I have used the following columns. To make it simple for you, I would use the same names. URL,ID,Category,Title,File,Blog Article,Highlight.

    The URLs I got from the export. So make sure to get those first to capture all posts.

    In case you don’t have this manual file no problem. You can still use the “backup page” of the report.

    3️⃣ Download+Open my Power BI template and add the correct 3 parameters. The file expects two paths for Excel files and one folder path, where all of your csvs are stored. For the folder include “\” at the end. For example: C:\User\USERNAME\documents\LinkedInSourceFolder\

    Conclusion

    My dual-faceted Power BI report serves a dual purpose: it acts as a magnifying glass over the intricacies of LinkedIn engagement while simultaneously reinventing the traditional CV. Hopefully no more need to update LinkedIn and at the same time my own personal CV.

    You want to have the template? Here it is: https://github.com/KornAlexander/PBI-Tools/blob/main/Ultimate%20LinkedIn%20Report.pbit

    Myths about Red-Green Deficiency in Visualizations

    Disclaimer: I am not an “Ophthalmologist”, so don’t cite me here and if you actually have a red-green blindness and/or disagree on the following article please ping me via LinkedIn. It is not as simple as it seems. 😊

    Color is a crucial element in data visualization and communication, significantly impacting how effectively information is conveyed. When discussing International Business Communication Standards (IBCS), I hear very often an argument against the use of red and green. The ongoing debate about the use of red and green, primarily due to concerns related to color deficiency needs to be cleared up. There are definitely incomplete arguments floating around. In this article, we’ll explore the misconceptions surrounding IBCS Red-Green Deficiency and how a more nuanced perspective can lead to a better communication.

    Understanding Color Deficiency

    Before delving into the myths, it’s important to grasp the concept of color deficiency and its prevalence. Approximately 8% of men and 0.5% of women of Northern European descent experience red-green color deficiency, also known as color deficiency, not blindness. Nevertheless, it’s vital to remember that not all cases are the same, and there’s a spectrum of color deficiency types, and varying degrees of severity. If I am not mistaken less than 1% of all are completely blind for red and green. And even less with 0.00001% being impacted by Rod Monochromacy, which is actually completely color blind.1

    The 2 Myths

    1. Myth: Sacrificing Pattern Recognition for a Fraction of your Target Audience
      • Since we know now the difference between being color blind and having a deficiency do we really sacrifice our reports for a minority and are there not better options?
      • Red and green are universally recognized as symbols for negative and positive, respectively—a concept deeply ingrained in our psyche. Eliminating these colors could lead to immediate loss of this intuitive “color coding”. There are more effective ways to enhance accessibility. Modern tools are capable of adapting. For instance, while red and green might be the default, an automatic switch to alternative colors can be implemented when necessary.
      • It’s important to note that even the 0.00001% can distinguish red and green if you help them. If you use red and green consistently than they should be able to differentiate them based on saturation. By consistently using highly saturated colors for red and less saturated for green, it could be possible to differentiate the colors based on saturation. However keeping in mind that if you choose a low saturation for green you will make it especially difficult for people with green-deficiency. An example of this approach is illustrated at the bottom of this article.
    2. Myth: The Challenge of Judging a KPI as Positive or Negative in Multi-Audience Reports
      • Another common fallacy is the assumption that in a report designed for multiple audiences, the judgment of a Key Performance Indicator (KPI) as positive or negative becomes ambiguous and difficult. However, this misconception arises from the belief that a report can be effective without a well-defined target audience.
      • In reality, the key to addressing this challenge is recognizing that a report should always be tailored to a specific target group. A report designed to cater to different audiences without a well-defined focus is destined to be subpar. By specifying the target audience, the report’s content and presentation can be tailored to meet the unique needs and expectations of that group.
      • And if you ever happen to really don’t know if a deviation has a positive or negative impact, than use a neutral color. IBCS never told us to not use any colors, just use it for a purpose and be consistent in what you are doing to achieve pattern recognition for better decision making.
      • Also, in the unlikely case you actually had to design a report for a multi-audience group than also go for a neutral color, if those different groups also perceive it differently. Like for example if you want to highlight increased “personal costs” within a PnL and your audience is work council + management of the company, than okay use a neutral color. But those examples are definitely the exceptions and not the norm.

    Practical Recommendations:

    1. Don’t pick green and red colors with both having a low saturation
    2. Consult Those Affected: When creating reports or data visualizations, consider seeking input from individuals with color deficiency. Their insights can prove invaluable in selecting appropriate color schemes and ensuring that the content remains accessible and inclusive.
    3. Print your Reports to test in Greyscale: To guarantee that your content remains legible even when viewed in black and white or grayscale, regularly assess your designs in these formats. Or alternatively even better go to a website like the following and test your dashboards there: https://pilestone.com/pages/color-blindness-simulator-1
    4. Use a neutral color if there is really no good or bad, like a blue.
    5. Relative deviation pins are hardest to grasp, always combine them with absolut bars in order to have more ink and bigger bars to give to your audience.

    Conclusion

    In conclusion, it is not as simple as it might seem. While concerns about color deficiency are valid, they should not lead to the blanket avoidance of red and green. Instead, a more nuanced approach that considers the specific context, audience, and accessibility needs should guide our choices. By consulting those affected, testing in greyscale, choosing contrasting shades of red and green, and resorting to neutral colors when necessary, we can strike a balance between effective communication and inclusivity in data visualization practices.

    Concrete tip: Those are the colors I personally use:

    • Microsoft Office Standard Red: 255, 0, 0 or #FF0000
    • Microsoft Office Standard Light Green: 146, 208, 80 or #92D050

    But please keep in mind that the dark green might work as well. Especially for people who are green-weak this is most likely the preferred color. Also even though it might be possible and worth considering to identify a color based on saturation, for people with deficiency high-saturation on high-saturation can be a good option too.

    1. https://www.colour-blindness.com/general/prevalence/ ↩︎

    Power BI – Pimp – Script

    Do you would like to apply data model best practices with a click of a button to your existing Power BI reports? Than the “PBI-Pimp-Script” is the right place for you!

    Edit: This script had a major revamp, published on 31. January 2024 and includes now a lot more: Explicit Measure Creation, Units calc group, Further Calendar Tables, Adding BPA and more.

    This script is designed to streamline and enhance your Power BI modeling experience. Whether you are a Power BI data model expert or just getting started, this script helps you supercharge your modeling efforts.

    Overview and Customization

    At the beginning the PBI-Pimp-Script offers through various prompts a range of enhancements that can be customized to fit your specific needs. Let’s dig into key aspects of this script and how you can tailor it to your requirements.

    Calculation Group for Time Intelligence Measures

    One of the essential features of this script is the ability to add a Calculation Group for “Time Intelligence”. Calculation Groups is a great way to organize or even reduce your measures, making it easier to navigate and manage your Power BI model. With this script, you can define a custom name for your Calculation Group, define the name of the date table and date column to be used. This makes sure your Time Intelligence Calculation Group works even if you are using non-standard names. Do you have a fiscal year and need fiscal year calculation items than the script offers the flexibility to adjust the cutoff day. In contrast you don’t need YTD, than decide against it.

    Date Dimension Table

    A robust Date Dimension Table is crucial for time-based analyses in Power BI. The PBI-Pimp-Script allows you to generate a Date Dimension Table and specify its name and the date column name to match your dataset’s structure. This script follows the approach to push the date dimension as far as possible into the backend. For the script this means this is not a calculated table and instead a power query date dimension. You need to make sure the current time selection 2018 till 2025 fits your needs.

    Empty Measure Table

    The script includes an option to generate an Empty Measure Table. Not sure this is the correct name, but that’s how I call this table. The table basically consists of nothing but two columns which are optional to be filled in. Both columns are by default hidden, that means you won’t immediately find this table. The purpose of this table is to work as container for all of your measures. In case the description of the measures is not sufficient, potentially you could also use this table to document your measures in the columns with editing the table directly in Tabular Editor. In case you need additional measure containers, make sure to rerun the script and stating Yes just for the empty measure table question. If you follow tabular modeling best practices than all of your fact tables contain zero visible columns. Therefore Empty Measure Tables is the way to go.

    Last Refresh Table

    Monitoring data refresh times is essential for data-driven decision-making. The script offers the option to create a Last Refresh Table, which keeps track of the last time your data was refreshed. This information can be invaluable for troubleshooting and ensuring that your data is up-to-date. You can use than this table to add a visualization to your report displaying also the last refresh time to your end-user.

    DAX Formatting

    Consistency is key when it comes to DAX (Data Analysis Expressions) formatting. The script allows you to format all calculation items and if you want also all measures in your model, ensuring that your DAX expressions are easy to read and maintain. This feature enhances collaboration and ensures that your entire team follows the same formatting conventions.

    And much more

    Now that you’re familiar with the powerful features of the PBI-Pimp-Script, let’s walk through the manual process of applying it to your Power BI model.

    Manual to Apply the Script

    1. Connect Tabular Editor (TE2) to PBI Report: Start by connecting with Tabular Editor (TE2) to your local Power BI instance, your Power BI report opened in PBI Desktop
    2. Save and Reopen .bim Locally with TE2: To ensure that you have the necessary access to the Power BI model, save and reopen the .bim file locally with TE2.
    3. Apply “Pimp-Script”: Copy+paste the “Pimp-Script” to enhance your Power BI model. Save it as Macro for reuse. The script will prompt you with various options for customization.
    4. Save PBIP: Save your Power BI project (PBIP) to preserve your changes.
    5. Ingest Model.bim into the PBIP File: Copy and replace the updated “Model.bim” into the respective “ReportName.dataset” folder of your Power BI project.
    6. Reopen PBIP File: Reopen your Power BI project file to see the improvements and enhanced modeling capabilities in action. You might need to apply minor fixes, like the relationship between fact tables and new date dimension.

    I sincerely hope the PBI-Pimp-Script, will help you to apply Power BI data modeling best practices even easier with just a few clicks.

    You need more Power BI data modeling best practices or have ideas to take the script further? –> Ping me via LinkedIn

    Here is the “Power BI-Pimp-Script”

    Video Walkthrough

    Video Walkthrough (German)

    Live Session

    Must-Have Certifications for a Power BI Expert

    Update (March 2026): This article has been updated to include the Fabric Data Engineer Associate (DP-700) certification and to mark the Azure Enterprise Data Analyst Associate (DP-500) as retired.

    In today’s rapidly evolving world of data analytics, certifications have become a valuable tool to showcase one’s expertise and knowledge. As a Power BI expert, you understand the importance of staying ahead in this competitive field. But which certifications are essential for a Power BI professional to possess? In this article, we will explore the must-have certifications that will not only validate your skills but also open doors to new opportunities.

    Microsoft Certifications:

    1. Power BI Data Analyst Associate: This certification is a no-brainer for any Power BI expert. It’s undoubtedly the most relevant and recognized certification in the field, focusing specifically on Power BI.
    2. Azure Enterprise Data Analyst Associate (retired): While not necessarily more challenging than the Power BI certification, this one delves deeper into the world of data analytics and covers a broader spectrum, including administrative tasks.
    3. Fabric Analytics Engineer Associate: This certification is essential for professionals focusing on the Microsoft Fabric analytics environment. It stands out for its emphasis on the end-to-end design and deployment of data analytics solutions at an enterprise scale. The certification requires mastery of Microsoft Fabric’s diverse components, including lakehouses, data warehouses, and dataflows, along with expertise in data transformation and exploratory analytics. It’s an ideal choice for those who want to excel in a role that combines deep technical knowledge with broad analytics capabilities, working alongside roles like data engineers, data scientists, and Power BI data analysts.
    4. Fabric Data Engineer Associate: The newest Fabric certification, introduced with Exam DP-700, targets data engineers working within the Microsoft Fabric ecosystem. It validates expertise in data ingestion, lakehouse and data warehouse implementation, real-time intelligence, and environment management. If you work with pipelines, notebooks, Spark, or KQL in Fabric, this certification proves you can design and optimize end-to-end data engineering solutions. It complements the Fabric Analytics Engineer certification perfectly — one focuses on analytics consumption, the other on data engineering foundations.
    5. Azure Administrator Associate: The Azure Administrator Associate certification is a valuable asset for Power BI professionals. Although it may not seem directly connected to Power BI, it demonstrates your competence in administering Azure services. Given that many organizations use Azure to support their data and analytics infrastructure, this certification can enhance your ability to work within the broader ecosystem that complements Power BI.
    6. Azure Solutions Architect Expert: For those aiming to excel in the world of Power BI and its related technologies, the Azure Solutions Architect Expert certification is a noteworthy achievement. While it may extend beyond the immediate scope of Power BI, this certification focuses on designing comprehensive solutions within the Azure cloud environment. This skill set can be highly advantageous, especially in scenarios where Power BI is integrated with Azure components. It positions you as a versatile expert capable of architecting end-to-end data solutions that encompass both Power BI and Azure, widening your skill repertoire for diverse data analytics projects. Just like the Azure Administrator the Azure Solution Architect Expert is particularly essential if your organization aims to achieve or maintain its status as a Microsoft Solutions Partner for Data & AI.

    Fundamental Microsoft Certifications:

    While there are fundamental certifications available, such as AZ-900 Microsoft Azure Fundamentals and PL-900 Microsoft Power Platform Fundamentals, they may not be as directly related to Power BI. Having the previous Associate and Expert certifications usually renders these fundamental certifications obsolete. Additionally, these fundamentals don’t count toward the Azure Solutions Partner status. The PL-900 might be relevant if you want to showcase basic knowledge in Power Apps or Power Automate which can be used in conjunction with Power BI.

    Renewal Considerations:

    It’s essential to keep in mind that certifications need to be renewed annually. Renewing them may not be as challenging as obtaining them initially, but it’s wise to focus on the most relevant certifications to maintain.

    IBCS Certifications:

    In addition to Microsoft certifications, IBCS (International Business Communication Standards) certifications are invaluable for any report designer, especially in the context of Power BI. IBCS certifications emphasize the importance of creating actionable reports that drive decision-making. More on the importance of IBCS in regards to Power BI here: The Importance of Actionable Reporting in BI & Reporting Best Practice IBCS – Blog about Actionable Reporting – Alexander Korn

    1. IBCS Certified Analyst: This certification covers the basics of IBCS and includes an inspiring one-day introduction to the framework. The remaining two days focus on hands-on training and workshops to ensure you can apply IBCS principles effectively. The IBCS Certified Analyst certification is awarded in perpetuity requiring no renewal.
    2. IBCS Certified Consultant: Once you’ve mastered IBCS and gained practical experience, you can aim for the IBCS Certified Consultant certification. The Analyst certification is a prerequisite for this advanced certification. It involves a comprehensive three-day course, during which you must demonstrate your expertise through presentations and written assessments. For the IBCS Certified Consultant certification, no direct test or course renewal is necessary, but attendance, including the fee, is required for the yearly IBCS Annual conference to maintain certification status.

    In conclusion, while there are numerous certifications available, the key is to choose the ones that align with your career goals and demonstrate your expertise as a Power BI professional. Microsoft certifications offer a solid technical foundation, while IBCS certifications enhance your ability to create actionable and impactful reports with any technology. Remember, certifications are not just badges; they are a testament to your commitment to excellence in the ever-evolving field of Business Intelligence.

    VendorCertificateDescription
    MicrosoftPL-300 Power BI Data Analyst AssociateThe Power BI Data Analyst Associate certification is essential for Power BI professionals. It focuses on Power BI’s core capabilities, making you an expert in creating data-driven insights.
    MicrosoftDP-600 Fabric Analytics Engineer AssociateThe Microsoft DP-600 exam, “Implementing Analytics Solutions Using Microsoft Fabric,” is for individuals skilled in designing and implementing data analytics solutions using Microsoft Fabric, covering lakehouses, data warehouses, and various analytics components
    MicrosoftDP-700 Fabric Data Engineer AssociateThe newest Fabric certification focusing on data engineering — covering data ingestion, lakehouses, data warehouses, real-time intelligence, and environment management using SQL, PySpark, and KQL within Microsoft Fabric.
    MicrosoftDP-500 Azure Enterprise Data Analyst Associate (retired)This certification delves deeper into data analytics and covers administrative tasks within the Azure ecosystem, complementing your Power BI expertise. It’s valuable if you work with Power BI in Azure-centric environments.
    MicrosoftAZ-104 Azure Administrator AssociateWhile not directly related to Power BI, this certification showcases your proficiency in managing Azure services. Many organizations use Azure in conjunction with Power BI, making this certification relevant for broader ecosystem knowledge.
    MicrosoftAZ-305 Azure Solutions Architect ExpertThis certification goes beyond Power BI but is vital for architects looking to design robust and scalable solutions within Azure, often used alongside Power BI. It’s especially crucial for organizations aiming for Data & AI Solutions Partner status.
    MicrosoftAZ-900 Microsoft Azure FundamentalsAn introductory certification to Azure, offering foundational knowledge that can be beneficial when working with Power BI in Azure environments.
    MicrosoftPL-900 Microsoft Power Platform FundamentalsWhile not Power BI-specific, it covers essential concepts that can enhance your overall proficiency in Power Platform applications, including Power BI, Power Apps and Power Automate.
    IBCSIBCS Certified AnalystThis certification emphasizes creating actionable reports, which is crucial for any effective reporting. It provides a deep understanding of International Business Communication Standards (IBCS).
    IBCSIBCS Certified ConsultantBuilding on the Analyst certification, this advanced course equips you with the skills to consult on IBCS implementation, enhancing your ability to deliver impactful reports.