Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |