PowerBI



PowerBI

I have been working with Power BI implementation for the last couple of months. It's been a great experience and great tool to create different kind of datasets and dashboards. 

There are two important areas which we need to cover in this post.

1) ETL using Power Query M formula language
2) Aggregation/calculations for reports using DAX Formula

1) ETL:

After loading the initial data into powerbi, we can manipulate the datasets as per our present requirement. This is equivalent into our traditional ETL process.

Example :

let
    
    Source = Sql.Database("example.database.windows.net", "EXAMPLE"),
    dbo_ISSUES = Source{[Schema="dbo",Item="ISSUES"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(dbo_ISSUES,{{"PRODUCT", "Product"}}}),
    #"Filtered Rows11" = Table.SelectRows(#"Renamed Columns", each true),
in <#"Filtered Rows2">

Power Query M formula language is optimized for building highly flexible data mashup queries as shown above. In this case, the output of every step is carry forward to the next step.

2) Aggregation/calculations for reports using DAX Formula:

Once you manipulate the initial data sets (like convert string into Date, create an extra column from any of the existing column etc.), you can write your formula to solve different requirements like calculate year-over-year growth compared to market trends, what is the total sales for month of Jan etc, 30days count, 60days count, Aging days etc..

Example :

60 Days = CALCULATE(COUNTROWS('REPORT'),'REPORT'[is60DaysAged]="yes",'REPORT'[Category]="Open")

Comments

Post a Comment

Please add your comment

Popular posts from this blog

ADF MODEL - VIEW CRITERIA (VC)

Image
ADF View Criteria: VIEW CRITERIA is nothing but a "Where" clause to the SQL query with some special functions". It will be used to refine the query results. View Criteria Types: =============== 1. Implicit View Criteria: Renders all the areas in the VO as a filter field. (Default) 2. Named View Criteria: Selected fields are participating in the filter fields section as per the configuration.    Key Technical Term: -------------------------- What is RowMatch :             RowMatch is nothing but "in-memory row filtering" OR CACHE What is Mode : [Filtering Option]            Query/Database - Use query's where clause. [Default]            Cache - In memory filtering            Both  - Use query or memory filtering. What is Bind Variables:           Parameters applied to querie...
Read more

Developing Scalable Web Applications with Cloud Architecture

When developing a web application, it's crucial to consider a simplified yet effective approach to the architecture, especially when leveraging the power of cloud platforms. Here are some essential steps and patterns to ensure a robust and scalable application: DNS : When you type <your domain>.com, the request goes to the DNS server, which responds with an IP address. It uses the cloud's global network to serve DNS zones from various locations worldwide, ensuring high availability and low latency for users. Web Servers : The IP obtained from the DNS server is used by the user's computer to make a connection to the web server where the front-end is deployed. Application Servers : The application's business logic is deployed on the application server. This may involve multiple functional microservices. Requests to this application server are usually limited to web servers and internal services. Database : The application needs one or more databases to store info...
Read more

Best Practices

Best Practices & Notes:  =================== please watch every day update : ------------------------------------- ADF - TABLE : Primary Key While working with table, ADF always expects a predefined primary key for the table.  We can create a primary key using DB sequence or other alternative options. If you do not have a PK for a table, during scrolling or selecting a row within the table or creating an empty row and scrolling to next set of rows, we will get a weird result. Example for Sequence PK: (Groovy Expression) (new oracle.jbo.server.SequenceImpl("TEST_SEQ",adf.object.getDBTransaction())).getSequenceNumber() Just a note: ADF - TABLE : Custom Partial Trigger We can add table selection listener programmatically with declarative component using the following snippets  of code : _getTable().addSelectionListener(new SelectionListener(){             @Override ...
Read more