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
Suryaann
Frequent Visitor

Group by but keeping the other column

Hi All,

 

Help Needed.

I have a table like below:

 

Date1         date 2         ID      Value

1/2/2019     5/2/2019    30       12

2/2/2019     5/2/2019    30       10

3/2/2019     5/2/2019    31       23

4/2/2019     5/2/2019     45      24

2/2/2019     6/2/2019     45      45

3/2/2019     6/2/2019     45      45

4/2/2019     6/2/2019    5         56

5/2/2019     6/2/2019     6        78

 

I want to perform the below steps:

 

1.Groupby date1 on max(date2)

2.create a line graph which plots the 12 months cumulative aggregate of value for the year(max(date 2).Here it is 2019

3.I want to add a slicer for ID which will make the line graph gets filtered for each ID selected.When i do groupby on dates the ID column is getting removed so am not able to establish a relation with other tables having ID column.That is i want the resulting table after groupby should contain a unique column to establish relationship.

 

Kindly help.Stuck up with this since 3days.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Suryaann ,

You can use following measure formula to calculate cumulative average based on current 'date 2' group and year:

Measure =
VAR currDate =
    MAX ( Table[Date2] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date2] = currDate
                && [Date1] < currDate
                && YEAR ( Table[Date1] ) = YEAR ( currDate )
        ),
        VALUES ( Table[ID] )
    )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Suryaann ,

You can use following measure formula to calculate cumulative average based on current 'date 2' group and year:

Measure =
VAR currDate =
    MAX ( Table[Date2] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Value] ),
        FILTER (
            ALLSELECTED ( Table ),
            [Date2] = currDate
                && [Date1] < currDate
                && YEAR ( Table[Date1] ) = YEAR ( currDate )
        ),
        VALUES ( Table[ID] )
    )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@Suryaann 

When you group by Date1, you want the max of Date2 returned. Got that. What happens when Date1 is the same but the ID is different? Wouldn't grouping by Date1 get confused about what to do with the ID column?

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.