Segmentation example

The goal of this example is to apply the following segmentation table to the measures of the data model:

A sample Power BI report based on the virtual relationship expressions could appear as follows:

• Three separate DAX measures are utilized for this example report:
  • A distinct count of customer countries and one for products, both filtered by the segmentation table
  • A sales growth tier metric that returns the text value of the Growth Tier column (for example, Underperform)

1. Create the Sales Growth Tier measure
   

Sales Growth Tier = 
VAR Growth = [Sales Growth]
VAR Level1 = CALCULATE(MIN('Sales Growth Tiers'[Max]),'Sales Growth Tiers'[GrowthTierKey] = 1)
VAR Level2 = CALCULATE(MIN('Sales Growth Tiers'[Max]),'Sales Growth Tiers'[GrowthTierKey] = 2)
VAR Level3 = CALCULATE(MIN('Sales Growth Tiers'[Max]),'Sales Growth Tiers'[GrowthTierKey] = 3)
VAR Level4 = CALCULATE(MIN('Sales Growth Tiers'[Max]),'Sales Growth Tiers'[GrowthTierKey] = 4)
RETURN 
SWITCH(TRUE(),ISBLANK(Growth), BLANK(),
 Growth <= Level1, 
 CALCULATE(VALUES('Sales Growth Tiers'[Growth Tier]),'Sales Growth Tiers'[GrowthTierKey] = 1), 
 Growth <= Level2, 
 CALCULATE(VALUES('Sales Growth Tiers'[Growth Tier]), 'Sales Growth Tiers'[GrowthTierKey] = 2),
 Growth <= Level3, 
 CALCULATE(VALUES('Sales Growth Tiers'[Growth Tier]),'Sales Growth Tiers'[GrowthTierKey] = 3),
 Growth <= Level4, 
 CALCULATE(VALUES('Sales Growth Tiers'[Growth Tier]),'Sales Growth Tiers'[GrowthTierKey] = 4),"Unknown")

• The existing Sales Growth Tier measure and the four segment threshold values are stored in DAX variables
• The SWITCH() function compares sales growth with the segment thresholds to assign the Growth Tier value

1. Create the virtual relationship measures
   

Customer Countries = 
CALCULATE(DISTINCTCOUNT(Customer[Country]),FILTER(ALL(Customer[Country]),
[Sales Growth] > MIN('Sales Growth Tiers'[Min]) && [Sales Growth] < MAX('Sales Growth Tiers'[Max])))

• The measures apply filters based on the segmentation table thresholds and the Sales Growth measure
• The virtual relationship measures can be used with the segmentation table in visuals per the example.