Relationships set up correctly?

[Anonymous]

Hello,

I am having the following issue and I am wondering if it's because my relationships aren't set up correctly?

I have 4 tables all from external systems apart from Dates which is a calculated table:

Registrations: one row per user registration to an online system.
Columns include UserID, Date, Region and Country.

Regions: list of regions the company is available.

Columns include RegionID, Country and Population.

Lookup: list of regions the company has user registrations in.

Columns include Region, RegionID and Country.

Dates table (Date, Month, Year etc)

Relationships between tables:

Dates[Date] : Registrations[Date] 1:Many

Lookup[Region] : Registrations[Region] 1:Many

Lookup[RegionID] : Regions[RegionID] Many:Many

I have a measure called Total Population which is SUM(Regions[Population]), which is the Value in the following visual table (table is showing Registration Date along the top and listing by Country in bold and drilled down by Region).

However the measure is only evaluating in months were there are more than 0 registrations in a particular Region. If there are 0 registrations in the month, the measure evaluates to a blank value.

Does anyone know how we can fix this so that even in months with 0 registrations, the Total Population will show correctly?

Thank you.

[v-kelly-msft]

Hi @Anonymous ,

Do you mean that if there's no registration,you wanna show as "0",such as below:

If so ,you just need to add 0 at the end of your measure: 

Total = SUM('Regions'[Population]) + 0

If not, do you mean that the calcalation returns an error value?If so, could you pls show me your sample data?

Best Regards,

Kelly

[Anonymous]

Hi @v-kelly-msft ,

Thanks for your reply but no, if there are no registrations I simply want the measure to show what it is showing in the other months, rather than a blank.

E.g. the row which has 30462, then the next month 30462, then the next month is blank for some reason - I need the cell to show 30462 instead.

Hope that makes sense.

[v-kelly-msft]

Hi  @Anonymous ,

If so, show me part of your data,and I will test for you.

Best Regards,

Kelly