Returning value from inactive related table

[decarsul]

Good day all,

Today i'm trying to eventually create a measure to show a sum of a value in a bar or line chart, where the value is only show once, based on the max of phase.

To do this, i need data from 2 tables. I cannot use calculated columns, because of using DirectQuery, so it has to be done in a measure. Table1 is a Facts table, Table2 is a Dimension table. Below an abbreviation of the tables.

Table 1
ID	Phase	etc.	etc.
1	1
1	2
1	3
2	1
3	1
3	2
4	1
5	1
5	2
5	3

Table 2
ID	Amount	etc.
1	20
2	31
3	647
4	684
5	645
6	54897
7	5646312
8	6846
9	465486
10	58

There is no active direct relationship between the 2 columns. There is however an in-active relation between the 2, based on ID column.

Now the question is. How will i get a table / measure that will show Table 1, but only the MAX of Phase. Like:

Table result
ID	Phase	Amount	etc
1	3	20
2	1	31
3	2	647
4	1	684
5	3	645

Eventually i want to have a bar chart. Where a Sum of Amount if shown as value, and Phase is shown on the X axis. So in this example, that would mean Phase 1 shows 710, Phase 2 shows 647 and Phase 3 shows 665.

I have tried using Summarize
SUMMARIZE(
Table1,
Table2[ID],
"temp1",
MAX(Table1[amount]))

and lookupvalue. (with and without userelationship)
CALCULATE(
LOOKUPVALUE(
Table2[Amount],
Table1[Phase], MAX(Table1[Phase]),
Table2[ID],VALUES(Table2[ID]])
), USERELATIONSHIP(table1,table2))

Would love to hear your thoughts!

[tamerj1 · Accepted solution]

@decarsul 
Here is the updated sample file https://we.tl/t-sttvJNdSEV

Amount Measure = 
VAR T1 =
    SUMMARIZE ( 'Table 1', 'Table 1'[ID], 'Table 1'[Phase] )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@MaxPhase", CALCULATE ( MAX ( 'Table 1'[Phase] ), ALLEXCEPT ( 'Table 1','Table 1'[ID] ) ),
        "@Amount", 
            CALCULATE (
                SUM ( 'Table 2'[Amount] ),
                USERELATIONSHIP ( 'Table 2'[ID], 'Table 1'[ID] ),
                CROSSFILTER ( 'Table 2'[ID], 'Table 1'[ID], BOTH )
            )
    )
VAR T3 = 
    FILTER ( T2, [Phase] = [@MaxPhase] )
RETURN
    SUMX ( T3, [@Amount] )

[decarsul]

Seems to work again. Time to validate!

Validated, works as intended. 

Thanks for the help!

[tamerj1]

Hi @decarsul 
I don't realy undestand the need for Table 2. Here is a solutio based on Table 1 with one added calculated column https://we.tl/t-MV57LgfmCG

Latest Phase = 
CALCULATE (
    MAX ( 'Table 1'[Phase] ),
    ALLEXCEPT ('Table 1', 'Table 1'[ID] )
)

Amount Measure = 
SUMX (
    VALUES ( 'Table 1'[ID] ),
    CALCULATE ( SELECTEDVALUE ( 'Table 1'[Amount] ) )
)

[decarsul]

Table 2 has the amount, where table 1 does not. Maybe it would have been better to not include that one in the sample above. As such, ill edit and remove that particular column to reduce confusion.

And as mentioned, since i'm using DirectQuery, i cannot create calculated columns, because the PowerBI Service which we publish to, does not support that.

[tamerj1]

@decarsul 
Here you go https://we.tl/t-iaJTiS2ZNa

Amount Measure = 
CALCULATE (
    SUM ( 'Table 2'[Amount] ),
    USERELATIONSHIP ( 'Table 2'[ID], 'Table 1'[ID] ),
    CROSSFILTER ( 'Table 2'[ID], 'Table 1'[ID], BOTH )
)

[decarsul]

Alright, that seems to work.

Wasn't aware off the crossfilter function.

Time for validation!

[tamerj1]

@decarsul 
Not sure about the result of the aggregation. It depends on your business logic but shall not an issue to amend.

[decarsul]

so i just validated. It doesn't add up.

I'm missing the MAX filter on the phase level.

Right now, its counting the amount per Phase, but i only want it to count based on MAX of the Phase.

Simply adding an MAX filter, doesn't work.

So how am i introducing the MAX for phase nr?