TREATAS to filter multiple columns in combination

[waeltken]

To the PBI Community,

My HR model has a pretty wide and long effective dated table of user records. It includes all historical user records for the past 6 years. I have a series of measures which essentially filter and deduplicate this "all-records" table based on an effective date parameter which is driven off of the Calendar table. The critical columns are [User ID], [Event Date] and [Last Modified Date]. When the table is refactored to the effective date parameter, the COUNTROWS or AVERAGEX, SUMX etc. operations take place to calculate e. g. Heacount at the end of each fiscal year or Average Salary at the end of each quarter etc.

Now I'm trying to optimize this filtering and deduplicating pre-amble operation on the whole table before the actual measure gets calculated. My idea was to calculate a slim filtering table finding the combination of [User ID], [Event Date] and [Last Modified Date] which are applicable for each user given a specific effective date parameter and then applying the filter table to the large aa-records table using TREATAS. 

I know TREATAS can filter multiple columns, but can/does TREATAS apply filters in combination with each other so that only those rows are returned where the unique combination of [User ID], [Event Date] and [Last Modified Date] are true?

Regards,

Henrik

[daXtreme]

"I know TREATAS can filter multiple columns, but can/does TREATAS apply filters in combination with each other so that only those rows are returned where the unique combination of [User ID], [Event Date] and [Last Modified Date] are true?"

When in doubt about how something works, it's always good to consult documentation: TREATAS - DAX Guide

[waeltken]

Here is what the output of the _basetable variable looks like. Now if I could only apply the [UniqueCombo] column to my ADDCOLUMNS-modified target table to filter it. Is used CALCULATETABLE and TREATAS, but would FILTER be more forgiving when it comes to using table variables and virtual columns in expressions/arguments?

- H

[Whitewater100]

Hello:

I can tell you FILTER doesn't trigger context transition like CALCULATETABLE but does take a table expression and condition(s). It's tough for me to answer as I'm not sure what your model looks like and what result you want. Usually there is a way of creating relationships without three columns concatenated.

You can pre-aggregate tables to make processing faster if that is helpful.

I'm sorry if I'm not completely understanding. 

[waeltken]

Whitewater - the three columns I want to apply in combination all sit in this virtual table that I have to calculate fresh for every query. Two nights ago I experimented with creating a fourth column in both the virtual as-of-date filtering table and my target table which concatenates [User Id] & [Event Date] & [Last Modified Date] in a single column which worked, but when I want to apply this single unique column from my virtual table that is stored in a table variable to my target table using CALCULATETABLE and TREATAS, the TREATAS function does not like me referencing to my virtual table and columns that come from a variable, telling me I need to supply a base table instead. Is it possible to make this work? I'm posting my whole table query below:

 EVALUATE

VAR _asofdate = Date(2020,10,26)

VAR _basetable =
    ADDCOLUMNS( 
        SUMMARIZECOLUMNS(
            SF_All__Records_Headcount[User Id],
            SF_All__Records_Headcount,
            "_basetable[MaxDate]", 
            CALCULATE(
               MAX ( SF_All__Records_Headcount[Event Date] ),
               FILTER(
                    SELECTCOLUMNS(
                    SF_All__Records_Headcount,
                    SF_All__Records_Headcount[User Id],
                    SF_All__Records_Headcount[Event Date]
                    ),
                    VAR vUser = SF_All__Records_Headcount[User Id]
                    VAR vMaxDate =
                    CALCULATE (
                        MAX ( SF_All__Records_Headcount[Event Date] ),
                        ALL ( SF_All__Records_Headcount ),
                        SF_All__Records_Headcount[User Id] = vUser,
                        SF_All__Records_Headcount[Event Date] <= _asofdate
                    )
                    RETURN
                    SF_All__Records_Headcount[User Id] = vUser &&
                    SF_All__Records_Headcount[Event Date] = vMaxDate
               )
              ),
              "_basetable[MaxModified]",
              CALCULATE(
               MAX ( SF_All__Records_Headcount[Last Modified On] ),
               FILTER(
                    SELECTCOLUMNS(
                    SF_All__Records_Headcount,
                    SF_All__Records_Headcount[User Id],
                    SF_All__Records_Headcount[Event Date],
                    SF_All__Records_Headcount[Last Modified On]
                    ),
                    VAR vUser = SF_All__Records_Headcount[User Id]
                    VAR vMaxDate =
                    CALCULATE (
                        MAX ( SF_All__Records_Headcount[Event Date] ),
                        ALL ( SF_All__Records_Headcount ),
                        SF_All__Records_Headcount[User Id] = vUser,
                        SF_All__Records_Headcount[Event Date] <= _asofdate
                    )
                    VAR vMaxMod =
                    CALCULATE (
                        MAX ( SF_All__Records_Headcount[Last Modified On] ),
                        ALL ( SF_All__Records_Headcount ),
                        SF_All__Records_Headcount[User Id] = vUser,
                        SF_All__Records_Headcount[Event Date] = vMaxdate
                    )
                    RETURN
                    SF_All__Records_Headcount[User Id] = vUser &&
                    SF_All__Records_Headcount[Event Date] = vMaxDate &&
                    SF_All__Records_Headcount[Last Modified On] = vMaxMod
               )
              )
          ),
          "_basetable[UniqueCombo]",
              ([User Id] & [MaxDate] & [MaxModified])
      )

VAR AllRecordsMod =

    ADDCOLUMNS(
        SF_All__Records_Headcount,
        "TargetUniqueCombo",
        (SF_All__Records_Headcount[User Id] & SF_All__Records_Headcount[Event Date] & SF_All__Records_Headcount[Last Modified On])
    )

VAR EffectiveTable =

    CALCULATETABLE(
        AllRecordsMod,
        TREATAS(_basetable[UniqueCombo],AllRecordsMod[TargetUniqueCombo])
    )


RETURN 
EffectiveTable 

[Whitewater100 · Accepted solution]

Hi Henrik:

You can use TREATAS if you can’t change data model. TREATAS can be continued for all the in common columns between the fact tables. INTERSECT and CONTAINS are the two other options for using DAX to move filters. (I guess USERELATIONSHIP in a way too) None are great for performance. A typical use case for TREATAS could be (notice common columns)

Sales  =
CALCULATE(
             SUMX( Sales, Sales[Quantity] * Sales[Unit Price]),
TREATAS(VALUES(Purchases[Brand], Sales[BrandName]), 
TREATAS(VALUES(Purchases[Color], Sales[ColorName])

So this is used in the case where two fact tables exist with no relationships. We make a report for purchases by brand using columns from Purchases and then use TREATAS to get Sales amount for same report.
Sales[Brand])

The way to normally solve this is to create middle table that joins these two fact tables as they both have product info.

E.G., 

Products = DISTINCT(
UNION(
ALL(Purchases[Product Name]), Purchases[Color]),
ALL(Sales[Product Name]), Sales[Color])
))

I hope this answers your question.