PowerBI - Creating a top5 chart from sharepoint list columns

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...