First example why we need it:
I want to create 2 groups one with profit over 500 and the other with 500 and bellow. So I create the following calculation :
In our case I used City and few measures (Row Count and Profit), as a result I have each city assigned to group:
In many cases I jut want the totals, for each group. So I'll remove the city and get a strange result:
What happened? Our 'Case 1' is treated as a measure and aggregated!
In Answers we had an option to mark "Treat as an attribute column":
The Attribute function should solve this problem.
The explicit syntax for this function is:
ATTRIBUTE(<expression> BY <attribute list> WHERE <predicate>)
The BY clause may be empty, in which case grand total grain is assumed.
I will return to our example. Instead of case when Profit>500 then 'Group A' else 'Group B' end
I will use case when Attribute(Profit by City)>500 then 'Group A' else 'Group B' end
Great.
Lets talk a bit about the BY part:
The Profit or any other measure is summarized according to the BY clause. In our case we wanted to summarize it by City. If we want it on any other level we should define it.For example in my data the primary key of the data is Order ID and Order Line ID. We can use them in the BY clause:
But this is exactly the case when we can omit the BY clause and get exactly the same result:
The wrong way to use Attribute function:
From what I have seen, the attribute function should be used as close to the measure as possible. For some reason, people (me including) tend to do it the other way, which seems to be the wrong way.In our example using Attribute function outside the Case function:
Will get you an error with some advise
| this can be resolved by having an administrator refresh the server metadata. |
| SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1; |
If, for some reason, you insist on this approach, the workaround is to use numeric values as result.
Or you can always use some cast function on top of it.
It will work:
Did I mention it's an ugly and not recommended approach?
