Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Relationships set up correctly?

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.

Need Help

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi  Anonymous ,

         

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

         

        Best Regards,

        Kelly