Forum Discussion
Aggregating with Slicers Related to Structured Columns
Hello,
I am connecting Power BI to a SharePoint Online List that has a dozen or so columns that permit multi-select choice and Person columns. For example (I can't show example of person b/c it'll show real people in my org...):
When I import this data into Power BI, it creates an ID column for me informing of the List row. I want to show visualizations that have, for example, Department as the x-axis, and sum of Amount as the Y-axis. Then I would also have slicers for other columns.
I have the option to expand the data or leave it as a list (or Table in the case of multi-select Person). If I leave the columns containing lists/tables, then I cannot seem to get the slicers or axes to work correctly. If I expand the columns, rows are duplicated and the aggregations are off.
Ideally, I thought I'd be able to find a way to create slicers that relate properly to values in unexpanded structured columns, but I haven't seen how to do that and Google is coming up short for me there.
If I expand the columns, I've been trying to create a measure that would groupby the List Row ID and then sum the first entry, but I'm struggling to figure out how to do that as well.
Any thoughts or suggestions?
Thank you
Problem resolved. I structured the schema differently and had to turn on bi-directional cross filtering.