Running Total measure using related column as category

[azul02]

I have the following measure to calculate a running total (count) across Creation Date.

Running Count of Documents = 
CALCULATE(
    COUNTA('Documents'[Document Id]),
    FILTER(
        ALLSELECTED(Documents),
        Documents[Creation Date] <= MAX(Documents[Creation Date])
    )
)

I then want to use this measure against a category from a related table called Person, that has a one-to-many relationship with Documents, but its giving odd results. The category is Person[Region (Latest Assignment)]. If I do a simple count on DocumentId I get this:

But the measure is producing this:

I'm missing something incredibly simple, I know, just in need of a nudge!

Edit: (sample tables)

Document Table

Document Id	Employee Number	Creation Date
3858923	10082	30/06/2020
3966823	10082	31/03/2021
3859624	10085	30/06/2020
3967604	10085	31/03/2021
3960264	10093	30/03/2021
3858532	10094	30/06/2020
3958365	10094	29/03/2021
3970496	10116	31/03/2021
4024651	10116	20/07/2021

Person Table

Employee Number	Region (Latest Assignment)
10082	East Asia Region
10085	East Asia Region
10093	Australasia Region
10094	East Asia Region
10116	UKIMEA Region

[aj1973 · Accepted solution]

Hi @azul02 

To your measure add a VAR _Region = SELECTEDVALUE(TABLE(Region**)) then add it to the filter table of CALCULATE.

[azul02 · Accepted solution]

Thanks Amine, but apologies as I'm struggling to see how to include the variable to filter as it doesn't allow me to compare against column from the Person table, given the context of the filter is the Document table.

[mangaus1111]

Hi @,

can you please send sample data of your 2 tables?

[azul02]

Update, thanks @mangaus1111 

[mangaus1111]

HI @azul02 ,

is your expected result like this?

[mangaus1111]

Hi @azul02 ,

is your expected result like this?

Region (Latest Assignment)	Count	RT
Australasia Region	1	1
UKIMEA Region	2	3
East Asia Region	6	9

[aj1973 · Accepted solution]

Hi @azul02 

To your measure add a VAR _Region = SELECTEDVALUE(TABLE(Region**)) then add it to the filter table of CALCULATE.

[azul02 · Accepted solution]

Thanks Amine, but apologies as I'm struggling to see how to include the variable to filter as it doesn't allow me to compare against column from the Person table, given the context of the filter is the Document table.

[aj1973 · Accepted solution]

Hi @azul02 

Is this correct to you?