Forum Discussion

dpc_development's avatar
dpc_development
Icon for Helper III rankHelper III
5 years ago
Solved

Including column from parent in Table visual messes up aggregation

Please see the attached excerpt: https://drive.google.com/file/d/1D24cyDv3Y9k2fY6EgtWAERFgWoA9uGxC/view?usp=sharing   I have a formula that calculates which of the rows in the table is the latest v...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous,

    You can create a measure as below:

     

    Latest event date = 
CALCULATE (
    MAX ( 'Unit Revision'[Event Date] ),
    FILTER (
        ALLSELECTED('Unit Revision'),
        'Unit Revision'[Unit Id] = SELECTEDVALUE ( 'Unit Revision'[Unit Id] )
            && 'Unit Revision'[Event Date] >= MIN ( 'Calendar'[Date] )
            && 'Unit Revision'[Event Date] <= MAX ( 'Calendar'[Date] )
    )
)

     

    Best Regards

dpc_development's avatar
dpc_development
Icon for Helper III rankHelper III
5 years ago

Hi Ashish_Mathur, thanks for your reply. I actually already know how to get the latest event date, it's using it to filter for the latest 'Unit Revision' row is where I am having problems. If you add 'Event Date' column to your Table visual or Gross Area, you'll notice four rows or the sum of all four rows, respectively. This is not the intended result. I need just the latest row.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous,

You can create a measure as below:

 

Latest event date = 
CALCULATE (
    MAX ( 'Unit Revision'[Event Date] ),
    FILTER (
        ALLSELECTED('Unit Revision'),
        'Unit Revision'[Unit Id] = SELECTEDVALUE ( 'Unit Revision'[Unit Id] )
            && 'Unit Revision'[Event Date] >= MIN ( 'Calendar'[Date] )
            && 'Unit Revision'[Event Date] <= MAX ( 'Calendar'[Date] )
    )
)

 

Best Regards