topic Re: Power BI count unique values in a column from SharePoint list in Custom Visuals Development Discussion

Power BI count unique values in a column from SharePoint list

Tentoria — Thu, 28 Dec 2023 09:12:12 GMT

Hi everyone,

I have a SharePoint list with multiple (choice) columns, each column has different values (C/CWF/DEV/NA). I want to divide the columns into 4 different bar charts where every chart displays on the X axis the names of the columns and the Y axis displays the bars where each bar shows the total number of different values in that specific column so I guess the bar would have 4 different colors.

Below is the table (it has more columns):

I want something similar to the below, basically I will need to create 4 of the below.

It is important that the charts update whnever a new entry is added to the list.

Thank you and I loof forward to your support.

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Sat, 30 Dec 2023 02:02:58 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Sat, 30 Dec 2023 10:11:50 GMT

FTT NAME	PTT NAME	PTT GRADE	DATE	Proactively supports co-trainer(s)	Follows lead trainer guidance /decisions	Communicates with lead trainer in a timely manner
User 1	PTT1	PUR	45267	C	C	C
User 1	PTT2	GR1	45266.3333	CWF	CWF	CWF
User 1	PTT3	CSV	45268	C	C	C
User 2	PTT4	PUR	45268	C	C	C
User 3	PTT5	PUR	45268	C	C	CWF
User 4	PTT6	CSV	45268	C	C	C
User 5	PTT7	PUR	45261	C	C	C
User 6	PTT8	GR1	45267.3333	DEV	C	C
User 7	PTT9	PUR	45268	C	C	C
User 1	PTT10	PUR	45268	C	C	C
User 1	PTT11	CSV	45268	C	C	C
User 3	PTT2	PUR	45268	C	C	C
User 2	PTT12	PUR	45268	C	C	C
User 8	PTT13	GR1	45268	C	C	C
User 9	PTT1	PUR	45287	C	C	C
User 4	PTT14	CSV	45287	C	C	C
User 5	PTT15	FG1	45294	C	C	C
User 2	PTT16	FG1	45301	C	C	C

Thank you for your reply lbendlin .

Above is a sample data of a bigger table that I have on the SharePoint list. Out of this data I am generating a Power BI dashboard like the below:

Everything is ok on the dashboard except for the 4 visuals (Collaboration, Knowledge, Delivery and Drive). Based on the sample table provided, the last 3 columns are under the collaboration visual which I want it to display like the visual below:

Help me with that and I will follow the same for the other 3 visuals.

Let me know if you need any more information.

Thank you 🙂

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Sun, 31 Dec 2023 14:43:00 GMT

Unpivot your data to bring it into usable format.

see attached

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Sun, 31 Dec 2023 15:35:31 GMT

Hi lbendlin ,

Thank you for your reply.

I did that and it worked fine for the 1st visual "Collaboration". A soon I did the same for the other 3 visuals (Knowledge, Delivery and Drive) it duplicated the rows resulting to innacurate values in the visuals. So instead of having a total of 18 for each column, it gave 2394 and other higher values. If I click on the row and remove duplicates it removes necessary rows which again results in inaccurate values.

Is there a way to share the file with you so you can have a better understanding and maybe fix it?

Thank you.

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Sun, 31 Dec 2023 16:04:38 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Sun, 31 Dec 2023 18:59:24 GMT

Hi lbendlin ,

FTT NAME

PTT NAME

PTT GRADE

DATE

COLLABORATION

Proactively supports co-trainer(s)

Follows lead trainer guidance /decisions

Communicates with lead trainer in a timely manner

KNOWLEDGE

Delivers correct SEP knowledge

Able to provide additional knowledge (apart from the OME)

Answers questions confidently and knowledgeably

DELIVERY

Able to objectively evaluate leaner(s) performance

Able to respectfully deliver objective /constructive feedback to learners during the practical sessions

Respectfully deliver theory sessions

Communicates with learners appropriately

Adheres to approved timetable /training material(s)

Manages all training resources efficiently

Hussein

Ahmed

PUR

07/12/2023 00:00:00

Hussein

El Ias

GR1

06/12/2023 00:00:00

CWF

Hussein

Wissam

CSV

08/12/2023 00:00:00

Tilina

Yvonne

PUR

08/12/2023 00:00:00

Tufan

Roxanne

PUR

08/12/2023 00:00:00

CWF

Trevor

Saba

CSV

08/12/2023 00:00:00

CWF

Xiyao

Sean

PUR

01/12/2023 00:00:00

Wael

Francesca

GR1

07/12/2023 00:00:00

DEV

Mary

Mijat

PUR

08/12/2023 00:00:00

Hussein

Michael

PUR

08/12/2023 00:00:00

Hussein

Janka

CSV

08/12/2023 00:00:00

Hussein

Deon

PUR

08/12/2023 00:00:00

Hussein

Manpreet

PUR

08/12/2023 00:00:00

Orsolya

Maleena

GR1

08/12/2023 00:00:00

CWF

Christian

Ahmed

PUR

27/12/2023 00:00:00

DEV

CWF

Hussein

Alexandra

CSV

27/12/2023 00:00:00

CWF

Hussein

Shauna

FG1

03/01/2024 00:00:00

CWF

DEV

CWF

Hussein

Lejla

FG1

10/01/2024 00:00:00

CWF

DEV

Hussein

Anna

GR1

11/01/2024 00:00:00

CWF

This is the table before unpivoting as I couldn't upload it due to the duplicates. Please unpivot the columns and see the duplicates.

Desired outcome:

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Sun, 31 Dec 2023 19:23:59 GMT

something like this?

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Sun, 31 Dec 2023 19:36:37 GMT

Yes!!

So how can I do it on my dashboard? as the table I have has more columns.

How can I share with you my pbix file?

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Sun, 31 Dec 2023 19:42:52 GMT

The process is the same, regardless of how many columns you have.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Mon, 01 Jan 2024 09:16:21 GMT

Here is the link to the file.Thank you and Happy new year 🙂

https://we.tl/t-MBT7wSj7DH

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Mon, 01 Jan 2024 14:43:44 GMT

Instead of unpivoting the individual sections separately you need to unpivot all at once.

Can you provide a sanitized extract of your SharePoint list?

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Mon, 01 Jan 2024 15:46:03 GMT

Hi lbendlin ,

I tried unpivoting all at once but how do I select which ones go in each visual?

Here is the link to sharepoint list : https://we.tl/t-I097XfqURB

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Wed, 03 Jan 2024 03:21:40 GMT

Hi lbendlin ,

I have provided the SharePoint list as requested. Did you manage to open it?

Re: Power BI count unique values in a column from SharePoint list

lbendlin — Wed, 03 Jan 2024 14:40:24 GMT

No, I have to check how to hande list templates, a first for me.

Re: Power BI count unique values in a column from SharePoint list

Tentoria — Wed, 03 Jan 2024 14:51:46 GMT

Appreciate your help 🙂