Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there!
I am analysing child fostering data, and want to work out the number of fostered children per 1,000 people aged below 15 in different areas
I have two data tables, 1 and 2.
1 has columns: area name; number of children in foster care
2 has columns: area name; total population aged under 15
I have managed to divide the SUM of children in foster care by the SUM of population aged under 15, but I need to do this for each area (row), as opposed to the whole column. I hope that makes sense. Here is my current measure, which doesn't take account of area:
Hi @V-lianl-msft , thanks for this!
The DAX you suggested has 90% solved the problem.
The issue is that a single-single relation is not possible, because my fostering data table contains not just counts of children in care, but also other measures, meaning for each area there are multiple rows. Similarly, my population data has multiple years.
In the query editor, I filtered out the other years from my population data, and the other measures from the foster care data. This allowed the DAX you suggested to return the correct value - however, it means i can't use any of my other data.
Do you know a way I can apply these filters using the measure (DAX) rather than the query editor, so that i can use all of my data? I hope that makes sense!
Thank you,
Jonny
Thanks Liang
I'm not quite sure how to share the .pbix here, and the data is on my company's sharepoint so i'm not sure if it would work anyway - i'll summarise the data here (the tables actually contain 1,000s of rows but are of the same format). Table 1 areas are English local areas + England itself, table 2 is just English local areas.
table 1:
Indicator | Area | Value |
Number of children in care | Tameside | 53 |
Number of children in care | England | 103,402 |
Number of places available | Tameside | 55 |
Number of places available | England | 106,321 |
table 2:
Date | Area | Population under 15 |
2019 | Tameside | 42,123 |
2018 | Tameside | 43,232 |
2019 | Manchester | 101,122 |
2018 | Manchester | 104,001 |
201X | Y English local area | N |
geography lookup table:
Sub-area | Sub-area type | Area | Area type |
Tameside | English local area | England | Country |
Manchester | English local area | England | Country |
Y | English local area | England | Country |
So, my measure needs to sum just one type of indicator, per each area, from table 1 - and dividie this by the sum of population under 15 per area from table 2. Does that make sense? One final issue is that table 2 does not have a row for England - the value for England is the sum of all the local areas.
Many thanks,
Jonny
In such cases , I prefer to create a column and bring the value in table .
Example
New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])
New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |