Including column from parent in Table visual messes up aggregation

[dpc_development]

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.

[dpc_development]

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 · Accepted solution]

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

[Ashish_Mathur]

Hi,

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

[dpc_development]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.