Cumulative distinct count with related table

[Anonymous]

Hi team,

I'm calculating distinct count of users logged in for last 7 days and cumulative of it.

Cumulative Unique User =

CALCULATE (

DISTINCTCOUNT('Login Info'[User Name]),

FILTER (

ALLSELECTED('Login Info'),

'Login Info'[End Date]

<= MAX ('Login Info'[End Date] )

)

My problem arises when i add Region in to the chart. Its showing the same cumulative for all regions.  Regions are from related table called 'Locations' which is combined based on the "username" column.

Kindly help me how can i alter my formula to get the region cumulative.

[amitchandak]

@Anonymous 

The formula seems right. But better you create a date table and join with End date an then use this formula

Cumulative Unique User =
CALCULATE (
DISTINCTCOUNT('Login Info'[User Name]),
FILTER (
ALLSELECTED('Date'),
'Date'[Date]
<= MAX ('Date'[Date] )
)

[Anonymous]

Yes. the formula is correct. However, i cant get it to work in my visual.

I'm using the custom visual "Small Multiple Line". when i add Regions into view, this visual creates multiple line charts with unique users count in line and date in x axis.  However the cumulative is not fixed per region. it has to calculate region wise cumulative. 

Any other idea?

[Anonymous · Accepted solution]

I could achieve it by amending my formula as below :

Cumulative Unique User =

CALCULATE (

DISTINCTCOUNT('Login Info'[User Name]),

FILTER (

ALL('Login Info'),

'Login Info'[End Date]

<= MAX ('Login Info'[End Date] )),FILTER(RELATEDTABLE(UserLocation),UserLocation[Region]=UserLocation[Region])

)