Group By with condition

[Anonymous]

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? 

[Greg_Deckler]

@Anonymous Just use a VAR for your table definition and then use that var in your SUMX expression. Your CALCULATE seems to be unnecessary.

[Anonymous]

@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

[Greg_Deckler · Accepted 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]

@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]
)