Forum Discussion
Helper VReturning value from inactive related table
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!
decarsul
Here is the updated sample file https://we.tl/t-sttvJNdSEVAmount 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] )
8 Replies
- tamerj1
Community Champion
decarsul
Here is the updated sample file https://we.tl/t-sttvJNdSEVAmount 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] )
decarsulSeems 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-MV57LgfmCGLatest 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-iaJTiS2ZNaAmount Measure = CALCULATE ( SUM ( 'Table 2'[Amount] ), USERELATIONSHIP ( 'Table 2'[ID], 'Table 1'[ID] ), CROSSFILTER ( 'Table 2'[ID], 'Table 1'[ID], BOTH ) )