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.

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

​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 othersAcademyUsercfg list columns: AuthorId, CountryAcademyStatus list columns: TrainingStatusIdId, AuthorId, TrainingId.TrainingId(lookup column from trainings), TrainingId.TrainingName(lookup column from trainings).AcademyLogs list columns: TrainingStatusIdId, AuthorIdCalendar powerbi table: normal calendar format typeRegionsFinalCount 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 =CALCULATESUMX(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 list2nd – trainingId with trainingId from logs listUsercfg list: 1st – country with country from RegionsFinalCount2nd – authorid with authorid from status list3rd – 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!  Read More

​