• Marek Vavrovic

TREATAS

The TREATAS function applies the result of a table expression as a filter to the columns of an unrelated table. In other words, you can use it to apply filters to a table while there is no relationship present. In a normal scenario, tables are related to each other and when one table is filtered, the filter will propagate using the relationship. With TREATAS, we can filter unrelated tables, which makes it a really useful function.


Syntax:

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )


Notes:

  • The number of columns specified must match the number of columns in the table expression and be in the same order.

  • Best for use when a relationship does not exist between the tables. If you have multiple relationships between the tables involved, consider using USERELATIONSHIP instead.

  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.


Data model and source tables


I will be working with 3 tables. Calendar contains the dates, 03Sales has dates granularity and 04Returns has month granularity. Tables 03Sales and 04Returns are unrelated tables, means no relationship between them and filter propagation is not working.



If I drag the Returns column (from 04Returns) into the table in the middle, together with the Sales column (from 03Sales) I will get back an incorrect result, the same total number for each row in the table.

Solution 1

TREATAS with VALUES function


syntax

VALUES(<TableNameOrColumnName>)


_Returns 1 =

CALCULATE (

(<target_measure>),

TREATAS (

VALUES ( <lookup_column> ),

<target__column> ))


_Returns 1 =

CALCULATE(

SUM('04Returns'[Returns]), TREATAS( VALUES('Calendar'[MonthNumber]), '04Returns'[MonthNumber]))


Solution 2

TREATAS with SUMMARIZE function


syntax:

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)


_Returns 2=

CALCULATE (

(<target_measure>),

TREATAS (

SUMMARIZE (

<lookup_table>

<lookup_column_1>

<lookup_column_2>

),

<target_column_1>,

<target_column_2>))


_Returns 2 =

CALCULATE(

SUM('04Returns'[Returns]), TREATAS( SUMMARIZE('Calendar','

Calendar'[Year],'Calendar'[MonthNumber]

), '04Returns'[Year ],'04Returns'[MonthNumber]))


Solution 3

FILTER with CONTAINS and VALUES functions


_Returns 3 =

CALCULATE (

(<target_measure>),

FILTER (

ALL ( <target_column> ),

CONTAINS (

VALUES ( <lookup_column> ),

<lookup_column>,

<target__column>)))

syntax:

FILTER(<table>,<filter>)

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)

VALUES(<TableNameOrColumnName>)


_Returns 3 =

CALCULATE (

SUM ( '04Returns'[Returns] ),

FILTER (

ALL ( '04Returns'[MonthNumber] ),

CONTAINS (

VALUES ( 'Calendar'[MonthNumber] ),

'Calendar'[MonthNumber], '04Returns'[MonthNumber])))


Solution 4

INTERSECT with VALUES function


_Returns 4=

CALCULATE (

(<target_measure>),

INTERSECT (

ALL ( <target__column> ),

VALUES ( <lookup__column> )

)

)


_Returns 4 = CALCULATE( SUM('04Returns'[Returns]), INTERSECT( ALL('04Returns'[MonthNumber]), VALUES('Calendar'[MonthNumber])))




6 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn