Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
waeltken
Helper I
Helper I

TREATAS to filter multiple columns in combination

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

1 ACCEPTED SOLUTION
Whitewater100
Solution Sage
Solution Sage

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.

 

View solution in original post

5 REPLIES 5
daXtreme
Solution Sage
Solution Sage

"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
Helper I
Helper I

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

 

waeltken_0-1649431452346.png

 

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
Helper I
Helper I

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
Solution Sage
Solution Sage

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors