How to track Entity Load and Readiness with Pipeline Execution Logs in Power BI

How to use Power BI DAX Logics to track Entity Load and Readiness Status with Pipeline Execution Logs

Scenario:
In modern data platforms, it is often necessary to track whether critical business entities have been successfully processed in the latest pipeline execution. 

Suppose we want to monitor the Load Status and Readiness Status of a defined list of entities such as:
dim_Customer, dim_Product, fact_Orders, fact_Inventory, dim_Supplier, fact_Payments, and fact_Order_Returns.

We can achieve this scenario by setting up two simple tables as per below. 

ref_Entities_to_Track:
It is a reference table which lists all the entities we expect to load in every pipeline run.

fact_Pipeline_Execution_Log:
It is a fact table, contains the details of each run, including the run date, pipeline name, entity key, entity name, pipeline run status, and entity refresh status.

Notes:  Assume that these tables do not have a direct relationship in the Data Model.

By comparing the reference table with the pipeline execution log for the latest run date (either today or the most recent available date), we can quickly identify the status of each entity. 

This shows whether an entity was successfully updated, failed to load, or was missing. 

Using the below measures, we can also derive a simple flag (1/0) for each entity to highlight its presence in the latest run.

Entity Availability Flag =

VAR _LatestRunDate =

CALCULATE(MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]))

VAR _EntityKey = MAX(ref_Entities_to_Track[Entity_Key])

RETURN

IF(

    COUNTROWS(

        FILTER(

            fact_Pipeline_Execution_Log,

            fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate &&

            fact_Pipeline_Execution_Log[Entity_Key] = _EntityKey

            )

        ) > 0,

        1,

        0

    )

Notes:
1: Indicates the Entity exists in the latest run.
0: Indicates the Entity is missing in the latest run.

For entities included in the latest run, we calculate the following measures to track their status:

• Entity_Refresh_Status: to show whether the entity had new updates, no updates, or a load failure.
• Pipeline_Run_Status: to show whether the pipeline succeeded, failed, or was missing.

Entity Refresh Status =

VAR _LatestRunDate =

CALCULATE(MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]))

VAR _EntityKey = MAX(ref_Entities_to_Track[Entity_Key])

RETURN

IF(

    [Entity Availability Flag] = 0,

    "Missing in Data Load",

    MAXX(

        FILTER(

            fact_Pipeline_Execution_Log,

            fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate &&

            fact_Pipeline_Execution_Log[Entity_Key] = _EntityKey

            ),

        fact_Pipeline_Execution_Log[Entity_Refresh_Status]

        )

    )

Pipeline Run Status =

VAR _LatestRunDate =

CALCULATE(MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]))

VAR _EntityKey = MAX(ref_Entities_to_Track[Entity_Key])

RETURN

IF(

    [Entity Availability Flag] = 0,

    "Not Avaiable",

    MAXX(

        FILTER(

            fact_Pipeline_Execution_Log,

            fact_Pipeline_Execution_Log[Pipeline_Run_Date] = _LatestRunDate &&

            fact_Pipeline_Execution_Log[Entity_Key] = _EntityKey

            ),

        fact_Pipeline_Execution_Log[Pipeline_Run_Status]

        )

    )

Result:

Notes:

We can identify the latest pipeline run date and assign a flag value of 1 or 0 using the below measures. 

By setting the [Latest RunDate Flag] measure to 1 for the most recent run date, Slicers in the Power BI can automatically default to that execution.
This ensures stakeholders always see the latest pipeline health information without manual effort.

Latest RunDate =

CALCULATE(

    MAX(fact_Pipeline_Execution_Log[Pipeline_Run_Date]),

    ALL(fact_Pipeline_Execution_Log)

    )

Latest RunDate Flag =

VAR _MaxRunDate =

    CALCULATE (

        MAX ( fact_Pipeline_Execution_Log[Pipeline_Run_Date] ),

        ALL ( fact_Pipeline_Execution_Log )

    )

VAR _Today = TODAY()

VAR _LatestRunDate = MIN ( _MaxRunDate, _Today )

RETURN

IF (

    SELECTEDVALUE ( fact_Pipeline_Execution_Log[Pipeline_Run_Date] ) = _LatestRunDate,

        1,

        0

    )

Business Value Creation / Insights:

From the above scenario, technical teams can derive practical insights into pipeline execution and data readiness. Dashboards in Power BI can highlight:

• Entities that were successfully refreshed with new data.
• Entities that had no changes.
• Entities that failed to load.
• Entities that were missing from the latest run.

This helps data engineers and support teams quickly detect issues, validate pipeline health, and take corrective actions. By automating this monitoring, they reduce manual checks, improve reliability, and ensure that downstream business users always receive consistent and trustworthy data.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to create and use User Defined Function for Currency Conversion in Power BI

How to apply the Currency Conversion in Power BI using User Defined Function
Scenario:

Imagine you are a global retail company analyzing sales across multiple countries. Your sales data is stored in a fact table (fct_Orders_Sample) with gross sales values in the base currency (USD). Business users want to view Gross Revenue in their local currency, either based on the country they select in a report or by explicitly passing a currency code (like GBP or EUR).

In Power BI, a User Defined Function (UDF) is a modular block of DAX logic that you can parameterize and reuse across multiple measures. Instead of repeating the same calculation in different places, you encapsulate it into a function and pass arguments to control its behavior.

We can achieve the above Scenario as explained in below Methods.

Data Model:

Relationships:

Notes:

ref_Currency_Mapping: This is a Currency Rate mapping table which is disconnected from the Data Model and will be used as Lookup table for the sample of Currency Conversion Rates.

ref_Currency_Mapping =

DATATABLE(

    "Currency_ID", INTEGER,

    "Country_ID", INTEGER,

    "Country_Name", STRING,

    "Currency_Code", STRING,

    "Conversion_Rate", DOUBLE,

    {

        {101, 234, "India",         "INR", 92.40},   -- 1 USD ≈ 92.4 INR

        {102, 235, "Japan",         "JPY", 157.00},  -- 1 USD ≈ 157 JPY

        {103, 236, "United States", "USD", 1.00},    -- Base Currency

        {104, 237, "Canada",        "CAD", 1.37},    -- 1 USD ≈ 1.37 CAD

        {105, 238, "United Kingdom","GBP", 0.75},    -- 1 USD ≈ 0.75 GBP

        {106, 239, "Spain",         "EUR", 0.85}     -- 1 USD ≈ 0.85 EUR

    }

)

Base Measure:

Gross Revenue = Sum(fct_Orders_Sample[Gross_Sales])

Note: The Default Currency for [Gross Revenue] is USD when there is no specific Currency selected.

Now we can do the Currency Conversion based on the above mapping table, using the below methods.

Method 1: Conversion Based on Selected Country:
In this Method, based on the selected Country from Slicer of a dim_Country[Country], we will apply the Conversion rate.

Selected Currency Conversion Rate =

VAR _SelCurCode= SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR _Conversion_Rate =

    LOOKUPVALUE(

        ref_Currency_Mapping[Conversion_Rate],

        ref_Currency_Mapping[Currency_Code], _SelCurCode

    )

RETURN

IF(ISBLANK(_Conversion_Rate), 1, _Conversion_Rate)

Gross Revenue (Selected Currency) =

[Gross Revenue] * [Selected Currency Conversion Rate]

We have used the below function for Dynamic Format for above measure.

fx_CurrencyFormat(BLANK())

The definition of the Function for Dynamic Currency Format:

fx_CurrencyFormat = ( CurrencyCode: string val) =>

VAR _Selected_Currency = SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR CurrencyCode = COALESCE(CurrencyCode, _Selected_Currency)

RETURN

    SWITCH(

        CurrencyCode,

        "INR", "₹ #,0.00",       -- Indian Rupee

        "JPY", "¥ #,0",          -- Japanese Yen

        "USD", "$ #,0.00",       -- US Dollar

        "CAD", "$ #,0.00",       -- Canadian Dollar

        "GBP", "£ #,0.00",       -- Pound Sterling (UK/England)

        "EUR", "€ #,0.00",       -- Euro (Spain, Eurozone)

        "$ #,0.00"               -- Default USD

    )

Result:

Method 2: Conversion Using a User Defined Function:
In this method, we will use a User Defined Function with a Currency Code Parameter, enables us to explicitly pass a specific Currency Code (e.g., always show GBP regardless of selected country).

In Power BI, we can create a User Defined Function from Model view as shown below.

The below Function is a Model dependent, which depends on the ref_Currency_Mapping table from the Data Model.

fx_CurrencyConversion = ( CurrencyCode: string val) =>

VAR _Selected_Currency = SELECTEDVALUE(dim_Country[Currency_Code], "USD")

VAR _Currency_Passed = COALESCE(CurrencyCode, _Selected_Currency)

VAR _Conversion_Rate =

    LOOKUPVALUE(

        ref_Currency_Mapping[Conversion_Rate],

        ref_Currency_Mapping[Currency_Code], _Currency_Passed

    )

RETURN

IF(ISBLANK(_Conversion_Rate), 1, _Conversion_Rate)

Gross Revenue (Selected Currency in fx) = [Gross Revenue] * fx_CurrencyConversion("GBP")

The below Function is used for the Dynamic format string:

fx_CurrencyFormat("GBP")

Result:

Note: 

You can customize this User Defined Function to suit your own scenario. Whether you are working with different currencies, adjusting for tax rates, or applying other business rules.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to create and use Calculation Groups in Power BI

How to define and use Calculation Groups in Power BI
A Calculation Group is a feature in Power BI that lets you define reusable logic once and apply it across multiple measures. 

Instead of writing separate DAX formulas for each measure (like [Sales] and [Orders]), we can create calculation items (such as Current Month, Previous Month, Month‑over‑Month Growth) that automatically work with any base measure.

Scenario:
Suppose we have 2 measures as [Sales] and [Orders] as base measures in our Data Model. For which we want to calculate the values for Previous Month and Monthly Growth by using the Calculation Groups, defined in Power BI Model as discussed below.

Data Model:

Relationships:

Base Measures:

Sales = Sum(fct_Orders_Sample[Gross_Sales])

Orders = COUNT(fct_Orders_Sample[Order_ID])

Now, we can define Calculation group with below Calculation Items to calculate the values for Previous Month and Monthly Growth.

The Calculation Groups can be defined from Model view of the Power BI:

MoM_Growth_Group: It is the Calculation Group Name

MoM_Growth_Field: It is the Column name of Calculation Group. This Column will be used in the Visuals to display the Calculation Items (Measures).

In the Table view, it will display as per below:

Here, Index is the Ordinal value. The default ordinal value for a calculation item/measure is '-1', and its Index starts from 0.

Current Month Value =

VAR _Enable_Flag = 1

VAR _Result =SELECTEDMEASURE()

RETURN

IF(_Enable_Flag, _Result)

Prev Month Value =

VAR _Enable_Flag = 1

VAR _Result =

CALCULATE(SELECTEDMEASURE(),

        DATEADD( Dim_Calendar[DimDate], -1, MONTH)

        )

RETURN

IF(_Enable_Flag, _Result)

% MoM Growth =

VAR _Enable_Flag = 1

VAR _CM =

    SELECTEDMEASURE()

VAR _PM =

    CALCULATE(

        SELECTEDMEASURE(),

        DATEADD(Dim_Calendar[DimDate], -1, MONTH)

    )

VAR _Growth = DIVIDE((_CM - _PM), _PM)

VAR _Msr_Format = FORMAT(_Growth, "00.0%;00.0%")

VAR _Result =

    SWITCH(

        TRUE(),

        ISBLANK(_Growth), _Msr_Format & " " & UNICHAR(9899), --Grey Circle

        ISBLANK(_CM), _Msr_Format & " " & UNICHAR(9899),     --Grey Circle

        _Growth = 1, _Msr_Format & " " & UNICHAR(9899),      --Grey Circle

        _Growth < 0, _Msr_Format & " " & UNICHAR(128308),    --Red Circle

        _Growth > 0, _Msr_Format & " " & UNICHAR(128994)     --Green Circle

    )

RETURN

IF(AND(_Enable_Flag, NOT ISBLANK(_Growth)), _Result)

Notes:

We can set _Enable_Flag = 0 to a disable or not to display a Calculation Item from Group.

The Dynamic format string "#.##" used for the Calculation Items [Current Month Value] and the [Previous Month Value].

The Dynamic format string "00.0%;00.0%" used for the Calculation Item [% MoM Growth]

The output of the Calculation Group and its Items is as per below. 

New Card Visual for Orders:

For the new Card Visual, we need to use the MoM_Growth_Field in the Categories section.

New Card Visual for Sales:

Matrix Visual for both Orders and Sales Measures:

For the Matrix Visual, we need to use the MoM_Growth_Field in the Columns section.

Notes:
1) Currently, the Calculation groups are only fully supported in visuals that can pivot measures dynamically, like the Matrix (and Pivot‑style visuals). For Table visual they will not work.

2) Calculation Groups in Power BI can override or disable default aggregations because they apply the transformations to measures at query time. 

This means the original aggregation logic (like SUM) may be replaced by the calculation group’s definition. To avoid unexpected results, always create and use explicit measures for the required aggregations.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------