CODEWITHSUNDEEP
powerbi
variable
variable

Reputation: 9714

When a unique column is on the table, you only need to apply a filter on that column to make the transition happen

Link: https://learn.microsoft.com/en-gb/learn/modules/dax-power-bi-modify-filter/5-context-transition

When a unique column is on the table, you only need to apply a filter on that column to make the transition happen. In this case, Power BI applies a filter on the CustomerKey column for the value in row context.

I am looking for an example to understand this.

Upvotes: 1

Views: 17

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Consider the following tables with and without a unique ID column.

Table1

ID ABC XYZ Value
1 a x 5
2 a x 2
3 a y 2
4 b y 2
5 b z 3

Table2

ABC XYZ Value
a x 5
a x 2
a y 2
b y 2
b z 3

Let's define measures

SumValue1 = SUM ( Table1[Value] )
SumValue2 = SUM ( Table2[Value] )

If you calculate each respective measure within the row context (e.g. using a measure in a calculated column) of the first row on each table, then the context transition will turn the row context into a filter context that looks like this for the first table

CALCULATE (
    [SumValue1],
    FILTER ( Table1, Table1[ID] = 1 )
)

but like this for the second table

CALCULATE (
    [SumValue2],
    FILTER (
        Table2,
        Table2[ABC] = "a"
            && Table2[XYZ] = "x"
            && Table2[Value] = 5
    )
)

Because there is no unique column in the second case, it's necessary to look at all of the columns to know which row we're referring to.

When there is a unique ID column, you can shortcut this since there is a one-to-one correspondence between rows and ID values, so that specifying the ID is equivalent to specifying the row and vice versa.

Upvotes: 1

Related Questions