cross filtering within the same table

[ravimerugu]

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

[parry2k · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[ravimerugu]

Thanks Ashish. It is along the same lines as what parry2k suggested. It resolved my issue.

[Ashish_Mathur]

You are welcome.

[parry2k · Accepted solution]

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.

[ravimerugu]

Thanks parry2k. It works!!