The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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!
Solved! Go to 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
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] )
Seems to work again. Time to validate!
Validated, works as intended.
Thanks for the help!
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] ) )
)
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.
@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 )
)
Alright, that seems to work.
Wasn't aware off the crossfilter function.
Time for validation!
@decarsul
Not sure about the result of the aggregation. It depends on your business logic but shall not an issue to amend.
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?