Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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? 

1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.