Forum Discussion

lennardheyder's avatar
lennardheyder
Frequent Visitor
4 years ago
Solved

Group table by max date and categories

Hey everybody,   I have a table with a deal ID, a customer, a product and a date (valid until). A customer can have several deals with the same product therefore I need an aggreagegated table which...
Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, lennardheyder 

    Try to create a calculated table as below:

    Table2 = 
GROUPBY (
    Table1,
    Table1[Customer],
    Table1[Deal ID],
    Table1[Product],
    "Max Date",
    MAXX ( CURRENTGROUP (), MAX ( Table1[Valid until] ) )
)

    or

    Table3 = 
SUMMARIZE( 
    Table1,
    Table1[Customer],
    Table1[Deal ID],
    Table1[Product],
    "Max Date",
    MAX ( Table1[Valid until] ) 
)

    GROUPBY

    SUMMARIZE

     

    Best Regards,
    Community Support Team _ Eason

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi lennardheyder 

 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

  • lennardheyder's avatar
    lennardheyder
    Frequent Visitor
    4 years ago

    Hey Vahid, I attached a table of the status quo and the target table. Thanks for the hint!

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, lennardheyder 

      Try to create a calculated table as below:

      Table2 = 
GROUPBY (
    Table1,
    Table1[Customer],
    Table1[Deal ID],
    Table1[Product],
    "Max Date",
    MAXX ( CURRENTGROUP (), MAX ( Table1[Valid until] ) )
)

      or

      Table3 = 
SUMMARIZE( 
    Table1,
    Table1[Customer],
    Table1[Deal ID],
    Table1[Product],
    "Max Date",
    MAX ( Table1[Valid until] ) 
)

      GROUPBY

      SUMMARIZE

       

      Best Regards,
      Community Support Team _ Eason