PowerBI - Creating a top5 chart from sharepoint list columns

[luispsmiranda96]

Hi guys!

Im having troubles here creating a top5 for my completed trainings, here is the situation:

The purpose of this file is to create KPIs from my sharepoint lists, one of them is the training that have a status of completed, having a card with the total completed trainings (already done) and a ring chart representing the top5.

Sharepoint lists:

AcademyTrainings list columns: TrainingId, TrainingName, and others

AcademyUsercfg list columns: AuthorId, Country

AcademyStatus list columns: TrainingStatusIdId, AuthorId, TrainingId.TrainingId(lookup column from trainings), TrainingId.TrainingName(lookup column from trainings).
AcademyLogs list columns: TrainingStatusIdId, AuthorId

Calendar powerbi table: normal calendar format type
RegionsFinalCount powerbi table: table with two columns -> Country, and Region, with every continent and country.

So my problem basically is, when I create an active relationship between my usercfg sp list, and my regions powerbi table, connecting Country - Country, my topn stops being a top5, as u can see in the image below, so it ignores the topn measure, and just exposes all the completed trainings. Here are the measures im using:

This is the table I created to only get, from the status list, the completed trainings. This way I get the individual quantity, and their name.

Completed Table = 

ADDCOLUMNS (

FILTER

'AcademyStatus',

),

'AcademyStatus' [TrainingStatusIdId] =

"Completed"

"Skills", RELATED (AcademyTrainings' [Skills])

-> this is counting all my trainings that have a status of completed.

Completeds = CALCULATE(COUNTROWS('AcademyStatus'), 'AcademyStatus'[TrainingStatusIdId] = "Completed") 

-> this is the topn measure I use for my top5.

Top5 =

CALCULATE

SUMX(

TOPN(

5,

),

),

ADDCOLUMNS (

),

VALUES ('AcademyStatus' [TrainingId. TrainingName]),

"CompletedCount", [CompletedTrainings]

[CompletedCount], DESC

[CompletedCount]

USERELATIONSHIP('AcademyStatus' [Created], 'Calendar [Date]),

ALLSELECTED('RegionsFinalCount [Region])

)

How should I correct it? I've tried everything.

Im gonna write down the relationships between all my tables/lists to complement the overall explanation:

AcademyTrainings:

1st - trainingId with trainingId.trainingId from Status list

2nd - trainingId with trainingId from logs list

Usercfg list: 

1st - country with country from RegionsFinalCount

2nd - authorid with authorid from status list

3rd - authorid with authorid from logs list

Basically, a user enters the website, stays registered in usercfg list, we get his name, country, etc. he can do several trainings, which come from academytraining list. Then, he completes a training, his action goes to logs and status list, the trainingName, trainingid, and trainingstatus. And what I wanna do here is to filter the completed trainings by a regions segmentation filter I created with the continents, im currently clicking on them and they all work, but the results aren't a top5, the active relationship between regionsfinalcount and usercfg list seems to kill it, but its the only way I got to be able to filter my top5 chart by region 😕

Can anyone help?

Thank you!

[#2]

To troubleshoot your issue, the behavior you’re seeing is likely due to how Power BI handles relationships and filters when calculating the top 5. When you introduce the active relationship between Usercfg and RegionsFinalCount, it likely changes how Power BI filters the data, causing the TOPN to behave incorrectly.

1. Check the Relationship between Usercfg and RegionsFinalCount
When you create an active relationship between Country in Usercfg and RegionsFinalCount, it might be applying unwanted filters to the top 5 measure. Consider setting this relationship to inactive and then use the USERELATIONSHIP function inside your top 5 measure, similar to how you’re using it with the Calendar table. This will allow you to control when this relationship is used.
2. Refactor the Top 5 Measure
There are several issues in the Top 5 measure, especially around CALCULATE and ALLSELECTED. Here’s a clearer approach:

DAX
Copy code
Top5CompletedTrainings =
CALCULATE (
SUMX (
TOPN (
5,
ADDCOLUMNS (
VALUES('AcademyStatus'[TrainingId.TrainingName]),
"CompletedCount", [Completeds] -- Completeds is your Completed Trainings measure
),
[CompletedCount], DESC
),
[CompletedCount]
),
USERELATIONSHIP('AcademyStatus'[Created], 'Calendar'[Date]), -- Ensure correct date relationship is used
ALLSELECTED('RegionsFinalCount'[Region]) -- Apply filtering for regions
)
3. Create a Separate Measure for Filtering by Region
If the ALLSELECTED filter isn’t working as expected, you may need to ensure the regions filter is applied correctly:

DAX
Copy code
RegionFilteredCompleteds =
CALCULATE (
[Completeds],
TREATAS (
VALUES('RegionsFinalCount'[Country]),
'Usercfg'[Country]
)
)
4. Use the Correct TOPN Syntax
Make sure the TOPN syntax is correct by referencing TrainingId.TrainingName as your target, ensuring the list is limited to the top 5.

5. Cross-Table Filtering
Be sure that the cross-table filtering between AcademyStatus, Usercfg, and RegionsFinalCount is properly managed. If any unwanted filters are applying, they might interfere with your top 5 calculations. If needed, you can use REMOVEFILTERS to control this.

Hope this solves your problem..

Dear TechCommunity Correct me if I'm wrong...