Values or relatedtable?

[newbiepowerbi]

Hello,

I have two tables:

Table 1

Obj ID	Contract type
1	Accrued
1	Accrued
1	Prepaid
2	Accrued
2	Accrued

Table 2:

Obj ID

1

2

I will create a SUMX measure in table 2, to compute for a value only if it does not contains in table 1 contract type "Prepaid". So basically it should compute for a value in Obj ID 2. 

So since it is a sumx function, it will iterate every row in table 2, ask if "Prepaid" in Table 1. My question is should i use Values(table1) so it will filtered should values pertaining to specific obj ID? do i need to put calculate so it can access table 1 and use the row in table 2 as a filter context? or should i use relatedtable? if yes i can select the contract type column to make my IN logic works?

[bpsearle]

Hey,

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

[newbiepowerbi]

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 @newbiepowerbi 

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 · Accepted solution]

Hi @newbiepowerbi 

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