Forum Discussion

barragan82's avatar
barragan82
Helper II
2 months ago

Cross-filtering for Long Format Data

Hi,

 

I have data that is arranged in "long" format. It is a survey where there is one row for every question answered rather than one row per respondent (User ID). I've included a screenshot of the spreadsheet. 

 

I created a report that includes a column chart for every question. The X-axis is Question Response and the Y-axis is Count of User ID. In the Filters pane, I included Question and filtered each column to each question accordingly. 

 

When I select a column within a chart to cross-filter/cross-highlight the reamining report, the other charts filter to zero. In the example below, when I select "Happened ever time" from the first chart the second chart should update to three (3) "Yes" but itstead does not display any data.

 

Any ideas how I can get the cross-filtering/highlighting to show on the other charts (without rearranging the excel spreadsheet)? Thank you for your help!

 

Report SetupResults

Need Help
Tips and Tricks

6 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    2 months ago

    This is the classic long-format cross-filter trap. Each chart already has its own Question filter, so when you click a bar, Power BI adds a Response filter to the underlying table. The other charts still carry their own Question filter, and the two together produce zero rows.

    The cleanest fix is to pivot the data to wide format inside Power Query, not in Excel. Reference your survey table, select the Question column, then Transform > Pivot Column with Response as the Values column. Set Advanced Options to Don't Aggregate. You will end up with one row per User ID and one column per question.

    Now each chart's X-axis is that question's column (Y stays Count of User ID), and you can drop the per-chart Question filter completely. Cross-filtering works as expected because clicking a bar filters the user rows, and the other charts redraw on the same user subset. Your source Excel file is untouched.

     

    If this helps, please mark it as the solution and give a kudos.

     

    Thanks,
    Shai Karmani

     

    Let's connect in LinkedIn

  • VijayP's avatar
    VijayP
    Community Champion
    2 months ago

    barragan82  I have noticed one thing in your data , each user id has same question repeated twice. I think this is with data quality. Try to share a sample data to give you better answer!

  • v-shchada-msft's avatar
    v-shchada-msft
    Community Support
    2 months ago

    Hi barragan82 
    Thank you for reaching out to the Microsoft Fabric Community Forum and thanks to Shai_Karmani for sharing helpful insights.

    Just checking in, were you able to resolve the issue? If not please feel free to share an update and we will be happy to assist further.
    Your feedback will also help others facing similar challenges.

    Thank you.

    • barragan82's avatar
      barragan82
      Helper II
      2 months ago

      Hi v-shchada-msft and Shai_Karmani,

       

      Thank you for your tips and for checking in! Unfortunately, I realize that this solution does not work because the excel spreadsheet has more columns that complicates the situation. I'm going to keep brainstorming, but thank you anyway for your help!

       

      Thank you!

  • v-shchada-msft's avatar
    v-shchada-msft
    Community Support
    2 months ago

    Hi barragan82,

     

    I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

    Thank you.