Forum Discussion
Group By with condition
I have 2 dimension tables "dim1" and "dim2", both connected to a fact table, factTab.
The columns in each table are as below: (ID1 and ID2 are whole numbers used for relationship to factTab)
factTab >> ID1 ID2 total_amount
----- -------- --------------
dim1 table >> ID1 startDate
----- --------
dim2 table >> ID2 endDate
----- --------
I want to calulate the sum of Total Amount in fact table by comparing the dim1[startDate] and dim2[endDate] without using RELATED function.
For this I created a calculated table "CalcTab "and then created a measure "OverallAmount " (and connected this new table to the other tables,) as below
Calculated Table
*******************
CalcTab =
GROUPBY(
factTab,
dim1[startDate],
dim2[endDate],
"TotalAmt",
SUMX(
CURRENTGROUP(),
factTab[total_amount]
)
)
Measure
**********
OverallAmount =
CALCULATE(
SUMX(
FILTER(
CalcTab ,
CalcTab[dim1startDate]<CalcTab[dim2endDate]),
CalcTab[TotalAmt]
)
)
==>>
Instead of creating a calculated table and measure seperately, (without using RELATED function) can I include the definition of the Calculated table also within the OverallAmount measure (inside the Filter)?, like a single measure that does all the work.
Also, is there a way to include the condition CalcTab[dim1startDate]<CalcTab[dim2endDate]) inside the Group by in CalcTab definition?
Anonymous If you do the GROUPBY as shown, you should be able to reference [startDate], [endDate] and [TotalAmt] columns. You would not use any kind of prefix when referencing these columns.
4 Replies
Anonymous Just use a VAR for your table definition and then use that var in your SUMX expression. Your CALCULATE seems to be unnecessary.
Greg_Deckler Thank you for the reply, I tried using Var but, I'm not able to refer to the columns inside the table variable for the comparison
Anonymous If you do the GROUPBY as shown, you should be able to reference [startDate], [endDate] and [TotalAmt] columns. You would not use any kind of prefix when referencing these columns.