Forum Discussion
Multi Tier Allocation from multiple tables
Hi JustDavid ,
Please follow these steps:
1.Use the following DAX expression to create a table
Table = FILTER ( SELECTCOLUMNS ( mirrorFactTableWithAllCostCodes, "id", [id], "obj code", [obj code], "cost code", [cost code] ), [id] = "fn9401" || [id] = "fn9402" )2.Use the following DAX expression to create a column in table 'mirrorFactTableWithAllCostCodes'
Column = VAR _result = SUMX(RELATEDTABLE(factTable),[Budget]) RETURN IF(ISBLANK(_result),0,_result)3.Use the following DAX expression to create a measure
MEASURE = VAR _obj = SELECTEDVALUE ( 'Table'[obj code] ) VAR _cost = SELECTEDVALUE ( 'Table'[cost code] ) VAR _id = SELECTEDVALUE ( 'Table'[id] ) VAR _table1 = ADDCOLUMNS ( 'tbl_Tier1', "c1", SUMX ( FILTER ( mirrorFactTableWithAllCostCodes, [id] = EARLIER ( tbl_Tier1[T1_From] ) && [obj code] = _obj && [cost code] = _cost ), [Column] ) * [T1_Alloc] ) VAR _table2 = ADDCOLUMNS ( 'tbl_Tier2', "c1", VAR _result = ( SUMX ( FILTER ( _table1, [T1_To] = EARLIER ( tbl_Tier2[T2_From] ) ), [c1] ) + SUMX ( FILTER ( 'mirrorFactTableWithAllCostCodes', [id] = EARLIER ( tbl_Tier2[T2_From] ) && [obj code] = _obj && [cost code] = _cost ), [Column] ) ) * [T2_Alloc] RETURN IF ( ISBLANK ( _result ), 0, _result ) ) VAR _table3 = ADDCOLUMNS ( 'tbl_Tier3', "c1", VAR _result = ( SUMX ( FILTER ( _table2, [T2_To] = EARLIER ( tbl_Tier3[T3_From] ) ), [c1] ) + SUMX ( FILTER ( 'mirrorFactTableWithAllCostCodes', [id] = EARLIER ( tbl_Tier3[T3_From] ) && [obj code] = _obj && [cost code] = _cost ), [Column] ) ) * [T3_Alloc] RETURN IF ( ISBLANK ( _result ), 0, _result ) ) RETURN SUMX ( FILTER ( _table3, [T3_To] = _id ), [c1] ) + SUMX ( FILTER ( 'mirrorFactTableWithAllCostCodes', [id] = _id && [cost code] = _cost && [obj code] = _obj ), [Column] )4.Final output
Best Regards
Hi JustDavid ,
Please follow these steps:
1.Use the following DAX expression to create a table
Table =
FILTER (
SELECTCOLUMNS (
mirrorFactTableWithAllCostCodes,
"id", [id],
"obj code", [obj code],
"cost code", [cost code]
),
[id] = "fn9401"
|| [id] = "fn9402"
)2.Use the following DAX expression to create a column in table 'mirrorFactTableWithAllCostCodes'
Column =
VAR _result = SUMX(RELATEDTABLE(factTable),[Budget])
RETURN IF(ISBLANK(_result),0,_result)3.Use the following DAX expression to create a measure
MEASURE =
VAR _obj =
SELECTEDVALUE ( 'Table'[obj code] )
VAR _cost =
SELECTEDVALUE ( 'Table'[cost code] )
VAR _id =
SELECTEDVALUE ( 'Table'[id] )
VAR _table1 =
ADDCOLUMNS (
'tbl_Tier1',
"c1",
SUMX (
FILTER (
mirrorFactTableWithAllCostCodes,
[id] = EARLIER ( tbl_Tier1[T1_From] )
&& [obj code] = _obj
&& [cost code] = _cost
),
[Column]
) * [T1_Alloc]
)
VAR _table2 =
ADDCOLUMNS (
'tbl_Tier2',
"c1",
VAR _result =
(
SUMX ( FILTER ( _table1, [T1_To] = EARLIER ( tbl_Tier2[T2_From] ) ), [c1] )
+ SUMX (
FILTER (
'mirrorFactTableWithAllCostCodes',
[id] = EARLIER ( tbl_Tier2[T2_From] )
&& [obj code] = _obj
&& [cost code] = _cost
),
[Column]
)
) * [T2_Alloc]
RETURN
IF ( ISBLANK ( _result ), 0, _result )
)
VAR _table3 =
ADDCOLUMNS (
'tbl_Tier3',
"c1",
VAR _result =
(
SUMX ( FILTER ( _table2, [T2_To] = EARLIER ( tbl_Tier3[T3_From] ) ), [c1] )
+ SUMX (
FILTER (
'mirrorFactTableWithAllCostCodes',
[id] = EARLIER ( tbl_Tier3[T3_From] )
&& [obj code] = _obj
&& [cost code] = _cost
),
[Column]
)
) * [T3_Alloc]
RETURN
IF ( ISBLANK ( _result ), 0, _result )
)
RETURN
SUMX ( FILTER ( _table3, [T3_To] = _id ), [c1] )
+ SUMX (
FILTER (
'mirrorFactTableWithAllCostCodes',
[id] = _id
&& [cost code] = _cost
&& [obj code] = _obj
),
[Column]
)
4.Final output
Best Regards
Anonymous ,
Thank you for your help.
A few questions for you.
- On your 1st point where you use DAX to create a table, in the formula you've manually identified the ids (screenshot below)
[id] = "fn9401" || [id] = "fn9402"​If i want to make this dynamic, how can I do this? (perhaps I create this table via PowerQuery) - I realized that you're able to achieve my 2nd view desired result via using this table that you've created via DAX, why can't I use the existing tables that I had?
- In your final output, when I change it from 'Table' to 'Matrix', the total in each of those ids and Grand Total are showing 0. Why is that?
- Assume if your way is the only way that I'd like to achive my result, looking at your 2nd formula which create a column in table 'mirrorFactTableWithAllCostCodes'
Column = VAR _result = SUMX(RELATEDTABLE(factTable),[Budget]) RETURN IF(ISBLANK(_result),0,_result)
if I had millions of rows, would PQ be much better in doing the lookup?
Hi JustDavid ,
1. Try this, it should change dynamically based on the value in the field "tbl_Tier3".
FILTER ( SELECTCOLUMNS ( mirrorFactTableWithAllCostCodes, "id", [id], "obj code", [obj code], "cost code", [cost code] ), [id] IN VALUES ( 'tbl_Tier3'[T3_To] ) )2.
why can't I use the existing tables that I had?Do you mean table 'mirrorFactTableWithAllCostCodes'?
It should work fine as well, but you'll need to modify the DAX expression to create a virtual table to replace this step. I created the new table just to facilitate my thought process.
3.The only filter conditions here are 'id', 'obj code', which corresponds to multiple 'cost code'.' selectedvalue' function returns the result only if it is a unique value. Otherwise the value returned is blank
4. I think it would be more efficient in PQ
Comparing DAX calculated columns with Power Query computed columns - SQLBI
Anonymous Thank you for your reply and answering my questions.
I have yet to apply the changes on the 1st and 2nd question, as I'm trying to replicate your thought process in PQ but to no avail (yes been trying this for a good 2 weeks).
Am wondering if I'm going to use your method, but instead of creating a calculated table, but using my exisiting 'mirrorFactTableWithAllCostCodes', how am I going to do it?Back to question 3, is there a way to have those automatically SUM rather than showing 0? This is what I was trying to accomplish for the past 2 weeks so that everything all flows.
Although I'm not new to DAX, but my skill is still a newbie. Been trying to figure out your formula that ADDCOLUMN, SUMX, FILTER and EARLIER and no the column return as 0. So can't really visualize what you're doing and what's the output is.