TREATAS failing in summarize table --- unless VALUES() replaced in VAR string --- anyone know why?

[charleshale]

Hi there.  I frequently use TREATAS with success when I have 2 separate very large tables (in this case, "Stripe" and "Posts") with the same user id convention.   A table I often run is looking at stripe # of transactions and comparing it to the # of posters.  Since at Patch.com you have to pay to post broadly, this is a way we measure cheaters -- people who are posting a huge amount but not paying to do so.      ANYWAY - in the instance below, the column with TREATAS is failing to see the row context in the non-summarize table and giving me the count of all rows in the table.   Can anyone see what I'm missing?   Note: user_id is the same convention and same data type in both tables 

Table =
VAR _MyTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Stripe', 'Stripe'[user_id] ),
        "StripeCount", CALCULATE ( COUNT ( Stripe[TransactionID] ) )
    )
VAR _MyTable2 =
    ADDCOLUMNS (
        _MyTable,
        "PostCount",
            CALCULATE (
                COUNTROWS ( Posts ),
                TREATAS ( VALUES ( Posts[user_id] ), 'Stripe'[user_id] )
            )
    )
RETURN
    _MyTable2

Thank you.   Charlie

[charleshale]

Agreed. It should work.   This might be the dreaded INT bug that pops up every now and then 

[charleshale]

AHA - I am not sure why the following makes a difference but here is the key:

The following fails to switch filter context:

....
ADDCOLUMNS(_MyTable,
                "@Transactions", CALCULATE([measure] ,    REMOVEFILTERS(), TREATAS( VALUES('Stripe'[user_id]), Posts[user_id])))
....

The following works:

...
ADDCOLUMNS(_MyTable,
        "@transactions", 
       VAR _UID = Stripe[User_ID]
       RETURN          
       CALCULATE([measure] ,    REMOVEFILTERS(), TREATAS( {_UID} , Posts[user_id])))

Brighter DAX minds may know why moving the VALUES() part of TREATAS() into a VAR fixes the context change.  I have no clue why.....but this is the only way it seems to work.

Brightest minds of DAX - any thoughts?

[PaulDBrown]

Do both your fact tables have dates?

[PaulDBrown]

CALCULATE Changes the context.

If I'm understanding what you are trying to achieve (compare the transactions with the number of posts by user id), why not set up the model with a User ID as a dimension and then simply use 2 COUNT measures?

Sample file attached

[charleshale]

Thanks, @PaulDBrown .  The reason I dont do that is that the user_id table doesn't have date elements so taking that approach requires intersecting with or cross joining a date table.   Since the user_id table is several million rows, I get really large cardinality doing that.

Interestingly, the following configurations DOES work.

Absuer2 = CALCULATETABLE (
    SUMMARIZECOLUMNS (
        'Stripe'[user_id],
        "@#EventsPosts", CALCULATE ([#Posts|NonDistinct|Event],
            TREATAS ( VALUES (  'Stripe'[user_id]), Posts[author_id] )),
        "@#Tx", [_.#Paid_Tx_Gross]
        )
    )

I am puzzled as to why the foregoing works but the summarize () at top doesn't.   It must be a data lineage issue with how TREATAS () works......but I'm not seeing what it is exactly.   It's an interesting issue

[PaulDBrown]

Why does the User ID table need dates? Can't you have a Date table as another dimension?

Also, the table you are constructing doesn't have any dates in it, so in effect it is the same as the table visual I posted.

[charleshale]

Good question.  The post table actually goes back 15 years and is 100m rows.  The idea is to see what the cheater ratio is like over time.   When I try to have a measure with dates under the following type of code, whereas I would think that the model's inherent date connections to the measure would calc, without a specific crossjoin, I am not getting the date context.   Any thoughts on how you would do that?   It's also odd that the CALCULATE TABLE () construction works with the TREATAS () but the SUMMARIZE () configuration doesn't.....but TREATAS can be tricky

Table = 
 ADDCOLUMNS (
    SUMMARIZE  ( User , USER_ID),
 "Measure", ........
)

Another issue: whereas SUMMARIZECOLUMNS() works for a table, it doesn't work in a measure (because of the ADDMISSINGITEMS() problem when using SUMMARIZECOLUMNS.  

Theoretically the following would work instead in a measure

Table = 
 ADDCOLUMNS (
    VALUES( STRIPE [USER_ID] ),
 "Measure", ........
)

But TREATAS () fails the change the filter context there whereas it does with SUMMARIZECOLUMNS()

[PaulDBrown · Accepted solution]

Maybe I'm completely misunderstanding what you are trying to do, but if the tables have dates, you can also create a Date Table as a dimension to use in the visuals (and measures, filters, slicers...) and simple COUNT measures will return what you are looking for.