Power BI Exercise - Creating a Virtual Relationship with TREATAS (Retail Dataset)

By infotech.razzi.my

Power BI Exercise - Creating a Virtual Relationship with TREATAS (Retail Dataset)

Use a disconnected Region Scenario slicer to filter retail sales without adding another model relationship.

In this guided exercise, you will create a disconnected region table and use TREATAS to transfer its selected values to DimRegion. You will compare ordinary and virtual filtering, test multiple selections, and verify the results with known retail totals.

Learning objectives

  • Explain the difference between a physical relationship and a virtual relationship.
  • Create a disconnected table for scenario selection.
  • Transfer one or more selected values with TREATAS.
  • Combine the virtual region filter with existing report filters.
  • Validate the measure in a visual and DAX Query View.

How the virtual relationship works

The Region Scenario table will remain disconnected. Its slicer cannot filter Sales by itself. Inside a measure, TREATAS takes the selected scenario regions and applies them as a filter to DimRegion[Region]. The existing physical relationship then carries that filter to Sales.

Virtual relationship created with TREATAS A Region Scenario slicer has no model relationship. A measure transfers its selected values with TREATAS to DimRegion, which filters Sales through an existing physical relationship. Region Scenario Disconnected slicer Central, East, North, South TREATAS Measure VALUES (selector) filters DimRegion[Region] physical relationship DimRegion → Sales Filters fact rows Returns scenario sales No physical relationship from Region Scenario to the model
Important: a virtual relationship is evaluated only when the measure runs. It does not create a relationship line in Model view.

Before you begin

Open the retail Power BI file used in the previous exercises. The model should contain Sales, DimRegion, and the measure [Total Sales]. Confirm that total sales equals 21,788.

Step 1Create the disconnected Region Scenario table

  1. Select Modeling > New table.
  2. Enter the following DAX:
Region Scenario =
DATATABLE (
    "Region", STRING,
    {
        { "Central" },
        { "East" },
        { "North" },
        { "South" }
    }
)

The values deliberately match the values in DimRegion[Region].

Step 2Keep the table disconnected

  1. Open Model view.
  2. Locate Region Scenario.
  3. Do not create a relationship from this table to any other table.
Check: if Power BI automatically creates a relationship, delete that relationship. This exercise requires a genuinely disconnected selector.

Step 3Create a selection-label measure

Create the following measure. It will display every active scenario region, including multi-select combinations.

Selected Region Scenario =
CONCATENATEX (
    VALUES ( 'Region Scenario'[Region] ),
    'Region Scenario'[Region],
    ", ",
    'Region Scenario'[Region],
    ASC
)

Step 4Prove that the slicer is disconnected

  1. Add Region Scenario[Region] to a slicer.
  2. Add [Total Sales] to a card.
  3. Select Central in the slicer.

The card should remain 21,788. This is the expected behavior: the disconnected table has no filter path to Sales.

Step 5Create the virtual-relationship measure

Scenario Region Sales =
CALCULATE (
    [Total Sales],
    TREATAS (
        VALUES ( 'Region Scenario'[Region] ),
        DimRegion[Region]
    )
)

Read the measure from the inside out:

  1. VALUES returns the region or regions active in the scenario slicer.
  2. TREATAS applies those values to DimRegion[Region].
  3. CALCULATE evaluates [Total Sales] under the transferred region filter.

Step 6Create the scenario-share measure

Scenario Region Share % =
DIVIDE (
    [Scenario Region Sales],
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( DimRegion )
    )
)

Format the measure as a percentage with two decimal places. The denominator removes the region filter but keeps other report filters, such as date, product category, and channel.

Step 7Build the comparison area

  1. Keep the Region Scenario[Region] slicer.
  2. Create cards for [Total Sales], [Scenario Region Sales], [Scenario Region Share %], and [Selected Region Scenario].
  3. Select one region at a time and compare the cards with the control totals below.
Slicer selectionTotal SalesScenario Region SalesScenario Region Share
Central21,7889,28142.60%
East21,7883,07814.13%
North21,7882,73412.55%
South21,7886,69530.73%
No selection / all regions21,78821,788100.00%

Step 8Test multiple selections

  1. Turn on multi-select for the scenario slicer if required.
  2. Select Central and South.
  3. Confirm that [Selected Region Scenario] displays both names.

The expected scenario sales is 15,976, and the expected share is 73.32%. [Total Sales] remains 21,788.

Step 9Combine virtual and physical filters

Add a slicer using the physical model field Sales[Channel] or its channel dimension. Select Central in the scenario slicer, then test the following:

Scenario regionChannelExpected Scenario Region Sales
CentralStore6,165
CentralOnline3,116
CentralAll channels9,281

This proves that the virtual region filter and the ordinary channel filter are combined in the same filter context.

Step 10Validate every region in DAX Query View

Open DAX Query View, enter the query, and select Run.

EVALUATE
SUMMARIZECOLUMNS (
    'Region Scenario'[Region],
    "Total Sales (unchanged)", [Total Sales],
    "Scenario Region Sales", [Scenario Region Sales],
    "Scenario Share", [Scenario Region Share %]
)
ORDER BY 'Region Scenario'[Region]

The query groups by the disconnected selector. Each row supplies one selector value, which the measure transfers to DimRegion. The ordinary total remains 21,788 on every row while the scenario measure changes.

Physical relationship, USERELATIONSHIP, or TREATAS?

TechniqueUse it whenKey behavior
Physical relationshipA stable model relationship should filter automatically.Visible in Model view and reusable by all suitable measures.
USERELATIONSHIPThe required relationship already exists but is inactive.Activates that existing relationship for one calculation.
TREATASNo model relationship exists and selected values must be transferred.Creates a virtual filter only during measure evaluation.
Modelling guidance: prefer a well-designed physical relationship for normal dimensions. Use TREATAS deliberately for disconnected scenarios, comparison selectors, or calculations that need virtual mappings.

Troubleshooting

The scenario measure always shows 21,788

Confirm that the slicer uses Region Scenario[Region], not DimRegion[Region]. Then check that the first argument of TREATAS reads the scenario table and its target is DimRegion[Region].

The scenario measure is blank

Check that the selector text matches the target values exactly. Values that do not exist in the target column are ignored.

Total Sales changes with the scenario slicer

The scenario table may have been related to the model. Return to Model view and remove that relationship.

Central and South do not add together

Allow multiple slicer selections and use VALUES, not SELECTEDVALUE, in the TREATAS measure.

Knowledge check

  1. Why does the disconnected slicer not change [Total Sales]?
  2. Which function returns all active slicer values for TREATAS?
  3. Where is the virtual relationship visible in Model view?
  4. Why is USERELATIONSHIP not the correct function in this exercise?
  5. What result should appear when Central and South are selected together?

Completion checklist

  • Region Scenario table created
  • No relationship added to Region Scenario
  • Scenario slicer added
  • Selection-label measure created
  • TREATAS measure created
  • Scenario-share measure formatted
  • Single selections validated
  • Multi-selection validated
  • Physical channel filter tested
  • DAX query results checked

Reference

Microsoft Learn: TREATAS function (DAX).