Forum Discussion
Best User-Friendly Horizontal Bar Graph Drill-Down Dashboard Methodology
How would I conceptually define the following in Power BI? Say I have two columns in a dataset. I would like to see a horizontal bar graph of number of rows sorted by the first column. When I click on a bar, I would like all the bar shapes to remain the same length; the bar I select should then act as if the legend parameter were the second column while greying out all the other bars. This should then revert back to a blank legend when I deselect a bar. I am open to other ideas if they work better.
After much searching around and experimentation, the best option that I found was a "Decomposition Tree - All Expanding" visual from the "Get More Visuals" button. (I then hid the display options at the top and set display units to none in the Visual/Data Labels settings.)
(I was able to work around the two columns being in separate data sets with no bidirectional join possible and the limitations of live online connections to two different datasets, using a variant of the following code.)
All on My ID column:
Portfolios 1 -> * Contacts (ALREADY THERE)
My ID Bridge 1 -> * Portfolios (New)
My ID Bridge 1 -> * Contacts (New and Inactive)
My ID Bridge Code:
My ID Bridge =
FILTER(
DISTINCT(
UNION(
SELECTCOLUMNS(
'Portfolios',
"My ID", 'Portfolios'[My ID]
),
SELECTCOLUMNS(
'Contacts',
"My ID", 'Contacts'[My ID]
)
)
),
NOT ISBLANK([My ID])
)
Add this measure to Contacts:
Contact Count =
CALCULATE(
COUNTROWS('Contacts'),
TREATAS(
VALUES('Portfolios'[My ID]),
'Contacts'[My ID]
)
)
I also added a flipside of this measure to Portfolios.
2 Replies
I’d use a DAX measure with a disconnected table to control the interaction.
The main idea is:
- Show row count by Column 1 in a horizontal bar chart.
- When a bar is selected, highlight that bar and use Column 2 as the legend.
- Keep the bar lengths unchanged and grey out the other bars.
- When the selection is cleared, return to the original view without a legend.
I think this would give a cleaner and more user-friendly experience than using the default drill-down.
After much searching around and experimentation, the best option that I found was a "Decomposition Tree - All Expanding" visual from the "Get More Visuals" button. (I then hid the display options at the top and set display units to none in the Visual/Data Labels settings.)
(I was able to work around the two columns being in separate data sets with no bidirectional join possible and the limitations of live online connections to two different datasets, using a variant of the following code.)
All on My ID column:
Portfolios 1 -> * Contacts (ALREADY THERE)
My ID Bridge 1 -> * Portfolios (New)
My ID Bridge 1 -> * Contacts (New and Inactive)
My ID Bridge Code:
My ID Bridge =
FILTER(
DISTINCT(
UNION(
SELECTCOLUMNS(
'Portfolios',
"My ID", 'Portfolios'[My ID]
),
SELECTCOLUMNS(
'Contacts',
"My ID", 'Contacts'[My ID]
)
)
),
NOT ISBLANK([My ID])
)
Add this measure to Contacts:
Contact Count =
CALCULATE(
COUNTROWS('Contacts'),
TREATAS(
VALUES('Portfolios'[My ID]),
'Contacts'[My ID]
)
)
I also added a flipside of this measure to Portfolios.