How to Create and Use Flags as Measures with DAX in Power BI

Using measures, we can perform calculations. However, in cases where we need to meet user requirements, such as highlighting the Sales Amount in a visual as green when it exceeds the Sales Target or as red when it falls short, this is where the concept of a “Measure as a Flag” becomes vital.

We define Sales_Flag as follows:

				
					Sales_Flag = IF (Sales [Sales Amount] > Sales [Sales Target], 1, 0)

By incorporating this measure into conditional formatting, we can assign a value of 1 for green and 0 for red coloration.

In this blog, we will delve into real-world applications of Measure as Flag, exploring various types of measures. Additionally, we will provide a distinction between measures and calculated columns in data modeling and visualization.

Implicit Vs. Explicit Measures

Implicit Measures

Implicit Measures are created by dragging numerical fields like (Profit, Quantity, Price, Sales Booking Amount) into the field well of a visual and then manually selecting the aggregation method, such as MAX, MIN, SUM, and so on, by right-clicking on the field.

Implicit measures can only be accessed within the particular visualization where they were created and cannot be referenced in other contexts.

Explicit Measures

Explicit Measures are created by using DAX functions as shown below:

				
					Sum Of Profit = SUM(Sales[Profit]).

You can use explicit measures throughout the entire report and incorporate them into other measures calculations as needed. If you want to calculate category Profit percentage as illustrated below:

				
					Sum Of Profit = SUM (Sales [Profit])
 %Profit = [Sum Of Profit]/CALCULATE ([Sum Of Profit],ALL(Product))

Measures vs. Calculated Columns

Measures

1. In Power BI, the measure is used to calculate aggregations and percentages or when you require a single value for a group of rows.

Example

				
					Average Salary = Average(Payroll[Salary])

2. Values are calculated based on the information derived from applied filters in the report or the fields used in visualizations. Which is called a Filter-Context.

3. It does not create new data in the table, ensuring the file’s size remains.

4. Recalculation occurs when the image below in the report is modified or when an additional column is either added to or removed from the visualization.

5. The measure name must be unique throughout the entire model.

Calculated Column

1. In Power BI, a calculated column calculates a specific value for each row.

Example

				
					Total Salary = Payroll[Salary] + Payroll[Allowance]

2. Values are calculated within the context of each individual row of a table, a concept known as Row-Context.

3. Adding specific values to each row in a table and storing them in the model can increase the file size and potentially slow down performance during data refresh and query execution.

4. Recalculation occurs either during a data source refresh or when changes are made to component columns that are used to derive calculated columns.

5. The new calculated column name must be unique at the table level.

In summary, create calculated columns in Power BI when introducing new computed columns to your dataset for various analytical purposes. However, be mindful of their potential impact on performance. Consider using measures in cases where aggregations and on-the-fly calculations are more suitable.

How to Create a Measure

Use a calculated measure when you need to compute percentage ratios or perform complex aggregations. To create a measure using DAX functions, click on the New Measure button located within the Home tab as shown in the image below.

When you create a measure, you will notice a new measure icon displayed in the Fields pane featuring the measure’s name. Simultaneously, the Formula Bar will automatically be populated with the measure’s name, allowing you to start writing DAX functions, as shown below.

Real-World Use Cases of Measure as a Flag

Use Case 1

The DimGeography table is used as a slicer for the Sales and Inventory fact tables. However, when you directly select the Location column from DimGeography, it displays all 300+ locations, even though the Sales table has only 100 and the Inventory table has 45 unique locations.

Users find it inconvenient to see locations in the slicer that do not exist in the fact tables. To address this, we need to create a measure flag that filters the slicer values to display only the locations that are present in the fact tables, providing a more relevant and user-friendly experience, as shown in the below DAX code.

				
					Common_Locations_Flag =

VAR Sales_Loc =
    VALUES ( FactSales[Location] ) //To Get a list of unique locations from the FactSales table
VAR Inventory_Loc =
    VALUES ( FactInventory[Location] ) //To Get a list of unique locations from the FactInventory table
VAR Common_Loc =
    UNION (
        INTERSECT ( Sales_Loc, Inventory_Loc ),
        //To Get the common values of the two lists
        EXCEPT ( Sales_Loc, Inventory_Loc ),
        //To Get the values from Sales_Loc that are not in Inventory_ Loc  
        EXCEPT (
            Inventory_Loc,
            Sales_Loc
        ) //To Get the values from Inventory_ Loc that are not in Sales_Loc 
    )
RETURN
    IF ( VALUES ( Geography[Location] ) IN Common_Loc, 1, 0 )
//Check if the location of the current row is in the list of common locations, and return 1 or 0 accordingly.

Finally, When you add the Common_Locations_Flag measure to the slicer visual filter pane and set it to 1, the slicer will display only those values in both fact tables, as shown in the image below.

Use Case 2

We have condition criteria to identify the total number of professionals and those actively engaged with clients. However, the user’s request is to specifically view a table visual that highlights professionals without client engagements.

To fulfill this request, we have implemented a Flag measure in DAX. This measure functions as a filter to isolate professionals who are not presently involved in client interactions, as shown in the DAX code.

				
					Unique_Professionals_Not_Engaged_Flag =

 VAR TotalProfessionals =
     CALCULATETABLE (
         VALUES ( Professionals[ID] ),
         Professionals[Segment] <> "Other Professionals",
         Companies[NAME] <> "General Office",
         Relationships[IS_CURRENT] = "TRUE",
         Professionals[Status] = "Active"
     )
 VAR UniqProfessionalsEng =
     CALCULATETABLE (
         VALUES ( Engagement[ID] ),
         Professionals[Segment] <> "Other Professionals",
         Relationships[IS_CURRENT] = "TRUE",
         Companies[NAME] <> "General Office",
         Professionals[Status] = "Active"
     )
 VAR ProfessionalsNotEng =
     EXCEPT ( TotalProfessionals, UniqProfessionalsEng )
 RETURN
     IF ( COUNTROWS ( ProfessionalsNotEng ) > 0, 1, 0 )

Use Case 3

Suppose a product-based industry wishes to assess equipment production performance within specific date ranges, such as today, month-to-date, quarter-to-date, etc. In that case, we can calculate these values using DAX functions like TODAY(), DATESMTD(), and DATESQTD(). However, if they want to analyze the performance of the last shift by the attribute [LastShift]="Y" to filter the data for the most recent shift.

In this scenario, a Flag measure can be employed on the period slicer and set to 1. When the user selects Last Shift, this Flag measure acts as a filter to the visualization to display data related to the last shift, as shown in the DAX code.

				
					Last_Shift__Measure_Flag =

IF (
    HASONEVALUE ( Parameter [Parameter Order] )
        && SELECTEDVALUE ( Parameter [Parameter Order] ) = "Last Shift",
    IF ( MAX ( Plant [LastShiftFlag] ) = "Y", "1", "0" ),
    "1")

Closing

In conclusion, using a measure as a flag allows you to perform dynamic conditional-based color formatting, filter the slicer to specific values, and more.

Measures optimize performance during data refresh and query execution compared to calculated columns, which can impact performance due to increased data storage.

If you need assistance to explore better ways of implementing measures using DAX, please contact our team of experts for support, guidance, and best-practice recommendations. Contact us today!

FAQs

What is the primary advantage of using measures over calculated columns when performing aggregations or complex calculations?

The primary advantage of using measures over calculated columns in Power BI is that measures are calculated on the fly based on the information derived from applied filters in the report or fields used in visualizations. This means measures provide dynamic, context-based calculations. In contrast, calculated columns are precomputed and may not adjust their values to changing conditions.

Why do you prefer explicit measures over implicit measures in Power BI?

The primary reason for using explicit measures over implicit measures in Power BI is that explicit measures can be reused throughout the entire report. In contrast, implicit measures are limited to the specific visual in which they are created.