TREATAS not working with SUM

[Anonymous]

Hi,

The below virtual relation did not work, is it because CustomerName is not amount?

How to correct this?

CustomerName =

CALCULATE(

    SUM(FactInternetSales[CustomerName]),

    TREATAS(VALUES(DimCustomer[CustomerKey]),FactInternetSales[CustomerKey]))

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

1.

CustomerName(Measure) = 
CALCULATE(
    MAX(FactInternetSales[CustomerName]),
    TREATAS( VALUES(DimCustomer[CustomerKey]), FactInternetSales[CustomerKey] )
)

2.

Measure = 
CALCULATE(
    MAX(FactInternetSales[CustomerName]),
    TREATAS( VALUES(FactInternetSales[CustomerKey]), DimCustomer[CustomerKey] )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

NOTWITHSTANDING that you are apparently summing text, TREATAS needs one of the resulting columns as it's input. 

CustomerName =

CALCULATE(

    SUM(FactInternetSales[CustomerName]); the output column is FactInternetSales[CustomerName], so that needs to be one of your TREATAS arguments, followed by the column in the other table column that you want to treat these results as. Also, if the value from the output column is missing from the TREATAS column, that value will still not be there. TREATAS can be tricky. 

[PaulDBrown]

@watkinnc 

I'm not sure I've understood what you are saying, but this statement is misleading at best

"CALCULATE(

    SUM(FactInternetSales[CustomerName]); the output column is FactInternetSales[CustomerName], so that needs to be one of your TREATAS arguments"

The arguments in TREATAS are columns from unrelated tables which you wish to "connect" as a filter expression (potentially a virtual one-to-many or one-to-one relationhsip - though the documentation actually simply defines the first input as "an expression that results in a table")

In other words, the "output column" needs not be included in the TREATAS expression at all.  In my example, 

Fact Customer Name = CALCULATE(
                    MAX(FactInternetSales[CustomerName]), 
                    TREATAS(VALUES(FactInternetSales[CustomerKey]), DimCustomer[CustomerKey]))

The "output column" or result is different from the arguments used in the TREATAS expression. The same would of course be true if using a measure of [Sum of Sales] for example (which would of  be the sum of a column called 'Table'[Sales])

IMHO, the easiest way to understand TREATAS is to think of it as establishing a virtual "one"-to-many or "one"-to-one relationship between two tables which are unrelated in the actual model (by using VALUES or DISTINCT in the first argument - or FILTER etc..). 

TREATAS may well be more sophisticated than I'm making it out to be, but this way of thinking about it makes it easy for me to use the function (which I do a lot).

[watkinnc]

Treats the columns of the input table as columns from other tables.For each column, filters out any values that are not present in its respective output column.

Syntax

TREATAS ( <Expression>, <ColumnName> [, <ColumnName> [, … ] ] )

PARAMETER ATTRIBUTES DESCRIPTION

Expression		The expression that generates the set of columns to be remapped.
ColumnName	Repeatable	The name of the output column.

Return values

TABLE An entire table or a table with one or more columns.

A table that contains all the rows in column(s) that are also in Expression.

Compatibility

• Power BI
  Current release
• SSAS Tabular
  • SSAS 2017
  • SSAS 2019
• Azure AS
  Current release
• SSDT
  Current release

» Show verified builds

Remarks

TREATAS assigns the data lineage of the columns returned by the expression using the columns in the following arguments. The result can be assigned to a variable, because TREATAS is not a filter modifier. The first argument must be a table expression.

The TREATAS function works in Excel since version 1809. However, the function is not reported by IntelliSense and it might be not supported in Excel by Microsoft, yet.

[manikumar34]

@Anonymous,

You are doing sum on string values. Try using 

DISTINCT or COUNT or DISTINCTCOUNT.

And string values cannot be shown on charts like bar charts, pie etc. You can only show them on table or matric sho try using COUNT or DISTINCTCOUNT if you want to show the numbers. 

Regards, 

Manikumar 

[Anonymous]

I tried all three of them, DISTINCT did not work no result, i wanted to return string in a table

The COUNT/DISTINCTCOUNT works but as numbers

Am i missing something?

[Anonymous]

I have attached a sample, I tried change DISTINCT to VALUES but still does not work

How do I bring CustomerName into DimCustomer table using virtual relationship? Any help is much appreciated

https://1drv.ms/u/s!AjxzXMIO4yRIhSyLtp_PdyRqw4wu?e=u9cItr

CustomerName = CALCULATE(

VALUES(FactInternetSales[CustomerName]),

TREATAS(VALUES(DimCustomer[CustomerKey]),FactInternetSales[CustomerKey]))

[PaulDBrown]

@Anonymous 

What exactly are you trying to do? What is your expected outcome?

VALUES and DISTINCT both return a list of values, which cannot be the output of a measure. A measure must return a single value. You can use VALUES or DISTINCT to create a new table for example, or as a part of a filter expression in a measure.

[Anonymous]

This is the expected outcome from virtual relationship, I created using column but receives error "A table of multiple values was supplied where a single value was expected

Maybe my syntax is wrong, I see a lot of blogs/post are using SUM with TREATAS/INTERSECT but my need is in string (not amount)

Are you able to download the sample pbix?

[PaulDBrown]

@Anonymous 

You cannot have a list as a result of a measure. If you need a string of values, you need to use CONCATENATEX.  
For your case, try using MAX instead of SUM in your measure

[Anonymous]

I still could not get it, should I remove the CALCULATE since I'm not calculate anything here? I'm just trying to get a string value which is CustomerName with virtual filter/relationship. Is there other way?

[PaulDBrown]

@Anonymous 

Using the file you included previously, here is what you get using TREATAS in this measure:

Fact Customer Name = CALCULATE(
                    MAX(FactInternetSales[CustomerName]), 
                    TREATAS(VALUES(FactInternetSales[CustomerKey]), DimCustomer[CustomerKey]))

Having said that, you could just create a relationship between you Dim Customer [Customer key] and you FactInternetSales [Customer key] in the model view. If you then add a table visual using the the Dim Customer [Customerkey] and the FactInternetSales[CustomerName] you will get the same result (and this is how you should do it really). No measure is then needed.

[Anonymous]

@PaulDBrown 

Thanks for the reply, I tried create your measure in the DimCustomer table but I could not replicate the same as yours? Instead I get all Peter. Did you have a relation CustomerKey connected at the back?

[PaulDBrown]

@Anonymous 

ok, so I now understand want you are trying to do. You are not looking for a measure, but a calculated column.

Is there a reason you need these tables to be unrelated? 

If not, I would create a new dimension table (instead of the Dim Customer) by referencing the FactInternetSales table in Power Query (select you FactInternetSales table by right clicking on the query for the table and select "reference"), remove all columns except CustomerID and CustomerName, select CustomerID and remove duplicates, and load to you model. Create a relationship between the new dimension table and your fact table by joining the fields for CustomerId in a one-to-many relationship and you are done (you can even hide the CustomerName field in your FactInternetSales table).

You don't need a calculated column if you follow this method (which is also actually a "best practice").

If you actually need the tables to be unrelated, can you please explain the context? What you are trying to achieve and why?

thanks.