Forum Discussion

ravimerugu's avatar
ravimerugu
Advocate II
8 years ago
Solved

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_IDQUESTION_NAMEMULTICHOICE_RESPONSE
8220224GenderMale
8220497Age18-24
8220497Autos - Income$85,000 to $89,999
8220497GenderFemale
8220494Age65+
8220494Autos - Income$25,000 to $29,999
8220494GenderMale
8220518Age25-34
8220518Autos - Income$35,000 to $39,999
8220518GenderFemale
8220526Age35-44
8220526Autos - Income$65,000 to $69,999
8220526GenderFemale
8220577Age45-54
8220577Autos - Income$140,000 or more
8220577GenderFemale
8220580Age55-64
8220580Autos - Income$80,000 to $84,999
8220580GenderMale
8220782Age18-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

  • 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.