Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
@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.
@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.
@Greg_Deckler It worked!! Thank you 🙂
But for comparison I had to refer to those dim table columns
OverallAmount =
var calctab=GROUPBY(
factTab,
dim1[startDate],
dim2[endDate],
"TotalAmt",
SUMX(
CURRENTGROUP(),
factTab[total_amount]
)
)
return
SUMX(
FILTER(
calctab, dim1[startDate]<dim2[endDate],
[TotalAmt]
)
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |