Forum Discussion
cross filtering within the same table
Hi,
I am new to Power BI. I am trying to create a report that has a map with the user count in different locations, two stacked bar charts for income range and age groups, and a pie chart for gender. This data is pulled from two tables, a user table and a demographics table. A sample data from the demographics table is given below. Data for the map is from the user table only. It's a one to many relationship between user table and demographics table. I am able to cross filter between the map and the pie chart and the stacked bar charts. It also works fine between income chart and map and age chart and the map. However, I am not able to cross filter age and gender visuals based on income changes or income and gender visuals based on age changes.
As you can see from the sample data from the demographics table shown below, filter used for the charts is the field QUESTION_NAME. How can I make the cross filter work across the page for all visuals in this case? Thanks in advance.
| USER_ID | QUESTION_NAME | MULTICHOICE_RESPONSE |
| 8220224 | Gender | Male |
| 8220497 | Age | 18-24 |
| 8220497 | Autos - Income | $85,000 to $89,999 |
| 8220497 | Gender | Female |
| 8220494 | Age | 65+ |
| 8220494 | Autos - Income | $25,000 to $29,999 |
| 8220494 | Gender | Male |
| 8220518 | Age | 25-34 |
| 8220518 | Autos - Income | $35,000 to $39,999 |
| 8220518 | Gender | Female |
| 8220526 | Age | 35-44 |
| 8220526 | Autos - Income | $65,000 to $69,999 |
| 8220526 | Gender | Female |
| 8220577 | Age | 45-54 |
| 8220577 | Autos - Income | $140,000 or more |
| 8220577 | Gender | Female |
| 8220580 | Age | 55-64 |
| 8220580 | Autos - Income | $80,000 to $84,999 |
| 8220580 | Gender | Male |
| 8220782 | Age | 18-24 |
basically you need to pivot your table whcih can be achieved thru query editor, here is the link for sample pbix I created from your data as reference.
Hi,
Your dataset is not well arranged. It should actually be a 4 column dataset with User_ID, Age, Autos - Income and Gender. If you do not know how to cary put the transformation, let me know and i will help.
5 Replies
- ravimeruguAdvocate II
Thanks parry2k. It works!!
- Ashish_MathurSuper User
Hi,
Your dataset is not well arranged. It should actually be a 4 column dataset with User_ID, Age, Autos - Income and Gender. If you do not know how to cary put the transformation, let me know and i will help.
- ravimeruguAdvocate II
Thanks Ashish. It is along the same lines as what parry2k suggested. It resolved my issue.
- Ashish_MathurSuper User
You are welcome.