How do I show instances of values from a SharePoint Choice Column as a pie chart in dashboard?

[Mister_A]

I'm trying to build a dashboard that will display info about the metadata tags on news stories in our SharePoint Intranet. The NewsHub is a subsite, each news story is a page and we have three custom choice columns where we tag each story with a value (or values) for News Categories, Strategic Priorities, DNA Behaviours.

I want to show three pie charts that show how many times tags from each of the three columns have been added to news stories. I thought this should be really simple, but it has been anything but ...

The story about "Assistants come together" has a total of seven metatags, but the dashboard is counting 12.

So, in QueryEditor, the three metadata columns looked like this:

What you'd expect, right? I couldn't just whack one of those columns into a pie chart because it doesn't work like that. So my first effort was to Expand the metadata columns to separate out the values ... 

Added the pie chart to the dashboard, populated it with the expanded News Category column ... but something wasn't right. In expanding the metadata column, it had added duplicated rows to the table behind the scenes, and the News Category tags were being counted multiple times. This increased geometrically when I expand the other two columns.

So I ended up with 12 rows for one news story and the metadata tags were getting counted multiple times.

My question is: how do I get the pie charts to count the metadata tags just once for each news story, so my news editors can see how many stories they've applied a particular tag to?

[Mister_A]

Forgive me ... but I was looking for a solution. Are you saying there isn't one?

[v-menakakota]

Hi @Mister_A ,

Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @lbendlin   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

Power BI can’t work directly with SharePoint’s multi-value choice fields because they’re stored as objects/lists, not scalar values. When those fields are expanded inside the same table, Power Query multiplies the rows, which is why your tag counts increase far beyond what you expect.

Use a SharePoint OData/REST query where the choice fields are already expanded server-side (much faster), or reshape the data into a proper many-to-many structure in Power BI, where tags are handled in their own table.

Best Regards, 
Community Support Team  

[Mister_A]

TBH, I still don't understand any of that, and wouldn't know where to start with a OData/Rest query or reshaping the data in a proper many-to-many structure. And, I dare say, that other less-experienced Power BI users like me wouldn't either.

As it turns out, while continuing to search for a solution elsewhere, I found that there was a way to get the pie-charts to show results that actually matched what I was seeing in the SharePoint source library. 

First, I created a new Measure, called it idCount, like this:

idCount = DISTINCTCOUNT('Site Pages'[ID])

Then, I added the pie-chart  visual to the page, setting the Legend to News categories.Label (the expanded column) and its Value to idCount. And, it works.

When I highlight the "Assistants" story in the table listing, it shows the right tags in the News Categories visual (and in the others that I set up, similarly).

I'm still wrestling with the Average Number of Tags panel (so my news editors can get an overview of how much they're tagging, and whether there's too few or too many tags going on). What I have at the moment is a Measure (tagsAverage) that calculates the number of tags, like this:

TagsAverage = (COUNTROWS(DISTINCT('Site Pages'[News categories.Label])))+

(COUNTROWS(DISTINCT('Site Pages'[Strategic priorities.Label])))+

(COUNTROWS(DISTINCT('Site Pages'[DNA.Label])))

I'm still trying to figure out how to turn that into an average across all the news stories for a selected date range. I'll probably get there eventually.

I hope this helps someone who's been battling a similar problem.

[v-menakakota · Accepted solution]

Hi @Mister_A ,

When you expand several multi-choice columns together, Power Query performs a cartesian join.

For example, If one story has 2 News Categories, 3 Strategic Priorities and 
2 DNA tags, after expanding all three at once you get.

2 × 3 × 2 = 12 rows

That’s why your story with 7 total tags turned into 12 rows and inflated your pie charts.

Please try below model structure.

Instead of expanding all columns in a single table, create separate tag tables.

1. News Category Tags table

2. Strategic Priority Tags table

3. DNA Tags table

Please follow below relationships in model.

Site Pages     1 --> *   News Category Tags
Site Pages     1 --> *   Strategic Priority Tags
Site Pages     1 --> *   DNA Tags

Note: This avoids cartesian duplication completely.

Please refer below measures to fix the issue.

1. Count tags for a single story

Tags Per Story =
VAR NewsCount =
    CALCULATE(
        DISTINCTCOUNT('Site Pages'[News categories.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
VAR StrategicCount =
    CALCULATE(
        DISTINCTCOUNT('Site Pages'[Strategic priorities.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
VAR DNACount =
    CALCULATE(
        DISTINCTCOUNT('Site Pages'[DNA.Label]),
        ALLEXCEPT('Site Pages', 'Site Pages'[ID])
    )
RETURN
    NewsCount + StrategicCount + DNACount

Note: ALLEXCEPT([ID]) restricts each DISTINCTCOUNT to a single story only, even if you have duplicate expanded rows.

2. Compute the average tags per story

Average Tags Per Story =
AVERAGEX(
    VALUES('Site Pages'[ID]),
    [Tags Per Story]
)

Best Regards, 
Community Support Team 

[Mister_A]

That's great ... thank you so much for that. The second part works perfectly.

I'll take a swing at expanding those three tags columns into separate tables later, when I have a little more time.

[lbendlin]

- Power BI only understands scalar data types, not objects.

- expanding fields in Power Query is very expensive.  Better run an ODATA query against your sharepoint with pre-expansion

- if you can have multiple tags per story then you are looking at *:* relationships. You may need multiple visuals that each look at your data from different perspectives.