Forum Discussion
Values or relatedtable?
Hi Anonymous
I’ve been looking at how you would add a calculated column to Table 2 that will do the calculation you want. However, I’m not sure how to do this. SUMX iterates across rows but we don’t actually want that. What we want to do is group by the [Obj ID] and within the group filter on the [contract type] and then with those results apply some computation. This then needs to link back to the row context in table 2.
The only way I can think of doing this is to use the calculated table that I provided the DAX for above. Then add a new calculated column to Table 2 and reference the [amount] column from the calculated table. DAX is “calc col in tbl2” = RELATED(‘calc table’[Amount])
Note that you need to create a relationship between Table2 and the calculated table on the [obj id] column.
Thanks, Brian
Resolver IIHey,
You have described the criteria for the filter that you want to use for your measure. What do you want to calculate with the measure?
The reason I ask is I’m not 100% sure if you want a measure or to add a calculated column to Table 2. It depends what you are trying to achieve overall and by that I mean how you want to display this data in your visuals.
Thanks, Brian
Hello Brian, though the example i used is quite simple, in actual data, will apply several conditions to table 2. My focus here is if i use a sumx measure for table 2, to only put value for each object id based on the following condition, if such object id contains contract type "prepaid" i will put 0 value, else i will put a value of 100. Basically, im trying to figure out the "in" function through a measure. Let me know if i'm clear with my goal.
- bpsearle
Hi Anonymous
How is table 2 produced? Does it already exist with columns in it?
The reason I ask is you can create Table 2 from Table 1 using SUMMARIZE. In my example, I’ve added an amount column to Table 1 so that some computation can be made for the example. You would replace that with whatever it is you need. Here is the example:
Table2 =
SUMMARIZE (
Table1,
Table1[Obj ID],
"Amount",
CALCULATE (
SUM ( Table1[Amount] ),
Table1[Contract type] = "Accrued"
)
)Thanks, Brian
- bpsearle
Hi Anonymous
I’ve been looking at how you would add a calculated column to Table 2 that will do the calculation you want. However, I’m not sure how to do this. SUMX iterates across rows but we don’t actually want that. What we want to do is group by the [Obj ID] and within the group filter on the [contract type] and then with those results apply some computation. This then needs to link back to the row context in table 2.
The only way I can think of doing this is to use the calculated table that I provided the DAX for above. Then add a new calculated column to Table 2 and reference the [amount] column from the calculated table. DAX is “calc col in tbl2” = RELATED(‘calc table’[Amount])
Note that you need to create a relationship between Table2 and the calculated table on the [obj id] column.
Thanks, Brian