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 value, given the 'To' value in the Date Slicer. If you move the 'To' date to a date in Jan 2021 or prior, the value 1 will be in a different row.

 

The formula works, but when I try to pull in the column value from the parent (Units table), the Table Visual shows all the units, even though I have filtered for a single unit. Even setting bi-directional relationship does not help.

 

Can someone explain why this behaviour occurs and how I can pull in Unit Code into the Table Visual that you see.

 

Thanks for your time.

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

5 Replies

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

    I feel this has something to do with relationships between the parent and child. Anyone has any clue?

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      Event Date should not be dragged to the visual.  It should be a measure.

    • 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