Forum Discussion
create a measure using data from two unrelated tables.
Hi
I need assistance to create a measure using data from two unrelated tables. Table 1 contains population data by year, 2013 to 2020. Table 2 contains data on the level of injures: “Deaths”, “serious injury” etc these records are by dates. I need a measure to calculate rate = counts of “Death”/population * 100,000. The calculate rate could be for the year as well as by quarter of a year. The population within a year is the same for all quarters of the year.
Here is the data
https://drive.google.com/open?id=1n84BTQ6_kXCuvClFylDW_jSyFFV29Tip
2 Replies
- amitchandak
Super User
Anonymous , typically the population remains the same in a year.
Create a date column and join it with the common date table
Date = Date(Year,1,1)
Population should be taken once per year, what month we select
You can try a measure like this
sumx(summarize('Population', Date[year], "_1", calculate(max('Population'[Population]), filter(All(Date), Date[Year] in values(Date[Date])))),[_1])
