none
Drill-through to a chart that is using pivoted data from the first chart, having trouble relating the data RRS feed

  • Question

  • I tried to post this on the official power BI forums but I'm having trouble logging into there. So I figured I'd post this question here.

    I've been tasked with generating some simple power bi reports for my organization. I currently have a flat .csv file that is structured with a number of entities (lets call them departments) on each row, with the columns listing investment return numbers for each of those departments, for different years.



    I was able to set up a simple bar chart showing all the different departments and their returns for a specific year by simply dragging the "entities" field to the axis and a specific year column to the values.



    However, I'm trying to set up a drill-through when one can right click any of the departments in the above chart and be brought to a separate page just for that department, which is another simple bar chart, but this time, instead of all the departments returns for one year, it will display all the years for this one department.



    I'm having trouble doing this because it seems the data needs to be pivoted to allow the different years to show for the one department on this one bar chart. I tried creating a separate table with the pivoted data, with which I can create the bar chart fine. However the problem is when linking to this page via drill-through from the "all department returns for one year" page. Because that data is coming from the data prior to it being pivoted.



    So if I drag the "department name" field (from the non-pivoted data which feeds the first page) to the drill-through filter on the secondary page (which is fed from the same data, but pivoted) it doesn't seem to establish the relationship correctly (and populate the secondary chart with the department name that was selected from the first. I believe this is because the non-pivoted data has the "department names" listed in one column as unique rows, while the pivoted data as the "department names" listed on one row as unique columns.



    Is there a way to somehow relate the pivoted data? Right now the only workaround I could figure out was to create 3 separate vizualizations side by side, each showing only one year for one department, and with that I was able to use the non-pivoted data, but obviously that is not ideal.

    Wednesday, July 22, 2020 2:30 PM

Answers

  • For anyone who finds this in the future and is having a similar issue, I was able to get this to work. Basically I had to unpivot the other columns and create a one-to-many relationship between the appropriate tables.
    • Marked as answer by KCSteele Monday, August 3, 2020 6:24 PM
    Monday, August 3, 2020 6:23 PM

All replies

  • it would be easier to answer if you could provide some sample of your data, and of the result you are looking for.
    Wednesday, July 22, 2020 3:11 PM
  • I'll try and explain with screenshots, I've curtailed the data for an example (this is all publicly available data).

    I'm just trying to create a simple drill-through from a main page to a secondary page. Here is the main page:

    Here are the data for that page:

    Here is the secondary page which I want to drill-through to:

    Here is the (pivoted) data for the secondary page (I had to pivot the data in order to get multiple years for one "Board" - otherwise, it only lists the year by count - maybe I'm missing something here)

    The problem is what to use for the drill-through field on the secondary page (where the question mark is). If I use "board name" from the initial data, it can't "pass" that to the second page, because a specific board (Adams) was already dropped into the field well. 

    The only workaround I could figure out was to create three separate visualizations, all using the non-pivoted data, and dragging the individual "2016" column to the first, "2017" to the second, "and "2018" to the third. This allowed the drill-through to be able to use the "board name" field as a drill-through field and successfully pass "Board name" to the second page. But using this method, I can't have all 3 years on the same visual. 

    I hope this makes sense.

    Wednesday, July 22, 2020 3:58 PM
  • Hi there. Please contact the Power BI team directly about the issues you're having signing into the Power BI Community site. This question seems like it would be better suited for that site instead of the PQ forum.

    Ehren

    Friday, July 31, 2020 7:00 PM
    Owner
  • For anyone who finds this in the future and is having a similar issue, I was able to get this to work. Basically I had to unpivot the other columns and create a one-to-many relationship between the appropriate tables.
    • Marked as answer by KCSteele Monday, August 3, 2020 6:24 PM
    Monday, August 3, 2020 6:23 PM