Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Summarize data according to max event_id

HI   my main table is  id, requisition_id, requisition_event_id, upload_link, created_by, updated_by, created_at, updated_at 220 99 1   504 504 07-10-2018 20:06 07-10-2018 20:0...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    After my test, you may try to this way as below:

    Step1: Add a calculate column

    Use EARLIER Function to find MAX("requisition_event_id") for each "requisition_id"

    MaxEventID = 
CALCULATE (
    MAX ( 'eta requisition_life_cycles'[requisition_event_id] ),
    FILTER (
        'eta requisition_life_cycles',
        'eta requisition_life_cycles'[requisition_id]
            = EARLIER ( 'eta requisition_life_cycles'[requisition_id] )
    )
)

    Step2: Add a calculate measure

    Add a measure that Count (requisition_id)

    Count of requisition_id = CALCULATE(COUNTA('eta requisition_life_cycles'[requisition_id]))

    Step3: Create a visual

    Drag the field MaxEventID and measure Count of requisition_id into visual

     

    Best Regards,
    Lin

     

     

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, Anonymous

After my test, you may try to this way as below:

Step1: Add a calculate column

Use EARLIER Function to find MAX("requisition_event_id") for each "requisition_id"

MaxEventID = 
CALCULATE (
    MAX ( 'eta requisition_life_cycles'[requisition_event_id] ),
    FILTER (
        'eta requisition_life_cycles',
        'eta requisition_life_cycles'[requisition_id]
            = EARLIER ( 'eta requisition_life_cycles'[requisition_id] )
    )
)

Step2: Add a calculate measure

Add a measure that Count (requisition_id)

Count of requisition_id = CALCULATE(COUNTA('eta requisition_life_cycles'[requisition_id]))

Step3: Create a visual

Drag the field MaxEventID and measure Count of requisition_id into visual

 

Best Regards,
Lin