Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
The below virtual relation did not work, is it because CustomerName is not amount?
How to correct this?
CustomerName =
Solved! Go to 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.
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.
NOTWITHSTANDING that you are apparently summing text, TREATAS needs one of the resulting columns as it's input.
CustomerName =
I'm not sure I've understood what you are saying, but this statement is misleading at best
"CALCULATE(
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).
Proud to be a Super User!
Paul on Linkedin.
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.
Expression | The expression that generates the set of columns to be remapped. | |
ColumnName | Repeatable | The name of the output column. |
A table that contains all the rows in column(s) that are also in Expression.
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.
@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
Proud to be a Super User!
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?
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
@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.
Proud to be a Super User!
Paul on Linkedin.
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?
@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
Proud to be a Super User!
Paul on Linkedin.
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?
@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.
Proud to be a Super User!
Paul on Linkedin.
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?
@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.
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |