Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with the number of students, which has filters for year, city, type of service, type of funding, etc.
I have another table with the population by city for every year.
i have a visual table in which i want to displaythe sum of the students by year (filtered by slicers), and the population by year (filtered only by city slicer). how can this be done?
example:
students
Year |City |service |funding |students
2019 |1 |1 |1 |10
2019 |2 |2 |1 |20
2019 |3 |1 |2 |30
2019 |1 |2 |2 |40
2018 |2 |1 |1 |15
2018 |3 |2 |2 |20
2017 |3 |2 |1 |10
population
year |city |population
2019 |1 |100
2019 |2 |120
2019 |3 |140
2018 |1 |110
2018 |2 |125
2018 |3 |150
2017 |1 |115
2017 |2 |135
2017 |3 |155
visual: (all slicers set on all)
year |students |population
2019 |100 |360
2018 |35 |385
2017 |10 |405
chaning the slicer value of service and funding should only change the students, but the slicer for city should change both population and students
does anyone know a way to do this?
Solved! Go to Solution.
Hi @im_deacha
take a look at the following solution. Attached pbix file at the bottom:
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @im_deacha
take a look at the following solution. Attached pbix file at the bottom:
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
thnx, this worked.
I had tried this but was adding the year from the students table instead from the year table, since i had already that one on the visual table. I didn't expect that to be an issue.
@im_deacha Are the tables related or not related to one another? If related, just put the related column in the visual. If unrelated, you will need to create a column or measure using LOOKUPVALUE, probably measure.
i tried adding a relationship using year-city as an id in population and adding it in students. but it duplicates the population in cases in which the same city has more then 1 service or funding for the same year.
i tried lookupvalue but couldnt get it to work, it wont accept the search value as students[year].
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |