Thursday, January 23, 2025

CI/CD pipeline for Power BI deployments

 A CI/CD pipeline for Power BI deployments is an automated process that streamlines the development, testing, and deployment of Power BI reports and dashboards across different environments (like development, testing, and production) by using tools like Azure DevOps to manage the workflow, ensuring consistent quality and minimizing manual intervention throughout the deployment process. 


Key components of a Power BI CI/CD pipeline:

  • Source Control:
    Storing Power BI PBIX files in a version control system like Git to track changes and manage different versions. 
  • Build Stage:
    • Validation Checks: Performing quality checks on Power BI files, such as data connectivity, data quality, and report layout. 
    • Packaging: Creating a deployment package containing the Power BI content ready for deployment. 
  • Test Stage:
    • Unit Testing: Running automated tests on individual components of the Power BI report (e.g., visuals, calculations) to identify issues early. 
    • User Acceptance Testing (UAT): Providing a controlled environment for end-users to test the functionality and usability of the Power BI content. 
  • Deployment Stage:
    • Environment Management: Deploying the Power BI content to different environments (development, staging, production) based on the pipeline stage.
    • Permissions Management: Assigning appropriate user access levels to Power BI workspaces and content based on the environment. 
  • Benefits of using a CI/CD pipeline for Power BI deployments:
    • Faster Time to Market:
      Streamlines the deployment process, allowing quicker delivery of Power BI reports and updates to users.
    Improved Quality:
    • Automated testing catches errors early in the development cycle, leading to higher quality deployments.
    Reduced Manual Effort:
    • Automates repetitive tasks, freeing up developers to focus on more complex aspects of Power BI development.
    Traceability:
    • Provides a clear audit trail of changes made to Power BI content and who made them. 
    Tools to implement a Power BI CI/CD pipeline:
    • Azure DevOps:
      A comprehensive platform with built-in features for CI/CD pipelines, including the ability to manage Power BI deployments.
    Power BI REST API:
    • Can be used to programmatically interact with Power BI service to automate deployment tasks. 
    Power BI Embedded:
    • For integrating Power BI content into custom applications, enabling more granular control over deployment. 

    Monday, January 18, 2021

    Visualization Types in Power BI

    Visualization Types in Power BI

    Below are just some of the many different types of visualizations that can be added to Power BI reports, specified in Q&A, and pinned to dashboards.

    Area charts: Basic (Layered) and Stacked

    The Basic Area chart is based on the line chart with the area between the axis and line filled in.

    area charts

    Bar and column charts

    Bar charts are the standard for looking at a specific value across different categories.

    bar chart

    column chart

    Cards: Multi row

    multi row card

    Cards: Single number

    number card

    Donut charts

    Donut charts are similar to pie charts. They show the relationship of parts to a whole.

    doughnut chart

    Gauge charts

    Displays current status in the context of a goal.

    radial gauge chart

    KPIs

    Displays progress toward a measurable goal.

    KPI chart

    Line charts

    Emphasize the overall shape of an entire series of values, usually over time.

    line chart

    Maps: Basic maps

    Used to associate both categorical and quantitative information with spatial locations.

    Basic map

    Matrix

    A table supports two dimensions, but a matrix makes it easier to display data meaningfully across multiple dimensions -- it supports a stepped layout. The matrix automatically aggregates the data and enables drill down.

    matrix

    Pie charts

    Pie charts show the relationship of parts to a whole.

    pie charts

    Q&A visual

    The Q&A visual lets you ask questions about your data using natural language.

    Q&A visuals

    Tables

    Work well with quantitative comparisons among items where there are many categories.

    table visual

    Treemaps

    Are charts of colored rectangles, with size representing value. They can be hierarchical, with rectangles nested within the main rectangles.

    treemap

    Waterfall charts

    Waterfall charts show a running total as values are added or subtracted.

    waterfall chart

    These are some of the out-of-the-box Power BI visuals available from the visualization pane in Power BI Desktop and Power BI Service. However, sometimes you need a more custom visual and these can be found in AppSource for Power BI.

    Tuesday, December 22, 2020

    Administrator roles related to Power BI

     Administrator roles related to Power BI

    There are several roles that work together to administer Power BI for your organization. Most admin roles are assigned in the Microsoft 365 admin center or by using PowerShell. The Power BI Premium Capacity and Power BI Embedded Capacity admin roles are assigned when the capacity is created. To learn more about each of the admin roles, 

    ADMINISTRATOR ROLES RELATED TO POWER BI
    Type of administratorAdministrative scopePower BI tasks
    Global adminMicrosoft 365Has unlimited access to all management features for the organization
    Assigns roles to other users
    Billing adminMicrosoft 365Manage subscriptions
    Purchase licenses
    License adminMicrosoft 365Assign or remove licenses for users
    User adminMicrosoft 365Create and manage users and groups
    Reset user passwords
    Power BI adminPower BI serviceFull access to Power BI management tasks
    Enable and disable Power BI features
    Report on usage and performance
    Review and manage auditing
    Power BI Premium Capacity adminA single Premium capacityAssign workspaces to the capacity
    Manage user permission to the capacity
    Manage workloads to configure memory usage
    Restart the capacity
    Power BI Embedded Capacity adminA single Embedded capacityAssign workspaces to the capacity
    Manage user permission to the capacity
    Manage workloads to configure memory usage
    Restart the capacity

    Monday, December 21, 2020

    Types of Filters in Power BI

    Types of Filters in Power BI 

    The four types of filters are:

    • Report – Applies to all pages in the report.

    • Page – Applies to all the visuals on the current report page.

    • Visual – Applies to a single visual on a report page. You only see visual level filters if you have selected a visual on the report canvas.

    • Drillthrough – Allows you to explore successively more detailed views within a single visual.

    Friday, December 18, 2020

    Parts of Power BI Desktop environment

     Parts of Power BI Desktop environment

    In Power BI Desktop, you'll begin to build reports in the Report view. You'll be working in five main areas:

    The five areas of Report view.

    1. Ribbon - Displays common tasks that are associated with reports and visualizations.
    2. Report view, or canvas - Where visualizations are created and arranged. You can switch between ReportData, and Model views by selecting the icons in the left column.
    3. Pages tab - Located along the bottom of the page, this area is where you would select or add a report page.
    4. Visualizations pane - Where you can change visualizations, customize colors or axes, apply filters, drag fields, and more.
    5. Fields pane - Where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.

    Basic building blocks in Power BI

     

    Basic building blocks in Power BI

    1. Visualizations – A visual representation of data, sometimes just called visuals
    2. Datasets – A collection of data that Power BI uses to create visualizations
    3. Reports – A collection of visuals from a dataset, spanning one or more pages
    4. Dashboards – A single-page collection of visuals built from a report
    5. Tiles – A single visualization on a report or dashboard

    Wednesday, September 23, 2020

    Power BI Level measures ALLEXCEPT

     Power BI Level measures 

    ALLEXCEPT


    Removes all context filters in the table except filters that have been applied to the specified columns.

    Syntax

    DAX
    ALLEXCEPT(<table>,<column>[,<column>[,…]])  
    

    Parameters

    PARAMETERS
    TermDefinition
    tableThe table over which all context filters are removed, except filters on those columns that are specified in subsequent arguments.
    columnThe column for which context filters must be preserved.

    The first argument to the ALLEXCEPT function must be a reference to a base table; all subsequent arguments must be references to base columns. You cannot use table expressions or column expressions with the ALLEXCEPT function.

    Return value

    A table with all filters removed except for the filters on the specified columns.

    Remarks

    • This function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.

    • As described in the following table, you can use the ALL and ALLEXCEPT functions in different scenarios.

      TABLE 2
      Function and usageDescription
      ALL(Table)Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value.
      ALL (Column[, Column[, …]])Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters.
      ALLEXCEPT(Table, Column1 [,Column2]...)Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.
    • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

    Example

    The following example presents a formula that you can use in a measure.

    The formula sums SalesAmount_USD and uses the ALLEXCEPT function to remove any context filters on the DateTime table except if the filter has been applied to the CalendarYear column.

    DAX
    = CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))  
    

    Because the formula uses ALLEXCEPT, whenever any column but CalendarYear from the table DateTime is used to slice the PivotTable, the formula will remove any slicer filters, providing a value equal to the sum of SalesAmount_USD for the column label value, as shown in Table 1.

    However, if the column CalendarYear is used to slice the PivotTable, the results are different. Because CalendarYear is specified as the argument to ALLEXCEPT, when the data is sliced on the year, a filter will be applied on years at the row level, as shown in Table 2. The user is encouraged to compare these tables to understand the behavior of ALLEXCEPT().


    This article is used from MSFT documents.

    Thursday, December 26, 2019

    Power BI Notes

    Free workspace  dataset refresh max of 8 times a day
    Premium workspace dataset refresh 48 times a day
    Power BI user roles: Admin, Member, Contributor

    Tuesday, December 24, 2019

    Switch Statement Power BI



    Column =
    SWITCH (
        TRUE (),
        'Table'[Field1] = "AAA", "RETAIL",
        'Table'[Field1] = "BBB", "ONLINE",
        SEARCH ( "CCC", 'Table'[Field2], 1, 0 ) = 1, and 'Table'[Field1] like '%yyy%', "RETAIL",
        'Table'[Field2] = "DDD", "RETAIL",
        "UNKNOWN"
    )


    Saturday, December 21, 2019

    Power BI Premium Calculator

    Power BI Premium Calculator


    AT times it will be confusing what kind of capacity like P1, P2 or P3 need to be purchased for the organization implementing Power BI, Microsoft link below makes it easy to calculate the costs just by entering the number of users:

    https://powerbi.microsoft.com/en-us/calculator/

    Friday, December 20, 2019

    Power BI DAX

    What is DAX?

    DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.

    Syntax

    Before you create your own formulas, let’s take a look at DAX formula syntax. Syntax includes the various elements that make up a formula, or more simply, how the formula is written. For example, here's a simple DAX formula for a measure:
    DAX formula syntax
    This formula includes the following syntax elements:
    A. The measure name, Total Sales.
    B. The equals sign operator (=), which indicates the beginning of the formula. When calculated, it will return a result.
    C. The DAX function SUM, which adds up all of the numbers in the Sales[SalesAmount] column. You’ll learn more about functions later.
    D. Parenthesis (), which surround an expression that contains one or more arguments. All functions require at least one argument. An argument passes a value to a function.
    E. The referenced table, Sales.
    F. The referenced column, [SalesAmount], in the Sales table. With this argument, the SUM function knows on which column to aggregate a SUM.

    This name is known as a fully qualified column name in that it includes the column name preceded by the table name.  Columns referenced in the same table don't require the table name be included in the formula, which can make long formulas that reference many columns shorter and easier to read. However, it's a good practice to include the table name in your measure formulas, even when in the same table.
    When trying to understand a DAX formula, it's often helpful to break down each of the elements into a language you think and speak every day. For example, you can read this formula as:
    For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.