Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter TOP N without duplicate

Hi Everyone,

 

I am a beginner in Power bi and I am desperate with filtering the TOP N.

 

I have a Table with three columns: Country, Asset Name, Expenses.

 

Now when I set the Filter Top N (5) in Asset Name and filter by value "Expenses" It shows me 7 not 5 Assets, because the Asset "Festival" appears 3 times.

 

 

Maybe someone could help me with my problem.

 

Thank you in Advance!

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Add the country code to the Asset Name to disambiguate. For instance, Festivals (PT), and use this instead of just Asset Name.

     

    Or create a measure:

     

    [Top5 Assets Filter] =
var __top5CountryAsset =
	CALCULATE(
		TOPN(5,
			SUMMARIZE(
				FactTable,
				Countries[Country],
				Assets[Asset Name]
			),
			[Expense], // must be a measure
			DESC
		),
		ALL( Countries ),
		ALL( Assets )
	)
var __currentCountry = SELECTEDVALUE( Countries[Country] )
var __currentAsset = SELECTEDVALUE( Assets[Asset Name] )
var __setOne =
	__top5CountryAsset in {
		(__currentCountry, __currentAsset)
	}
	&&
	{
		(__currentCountry, __currentAsset)
	} in __top5CountryAsset
return
	1 * __setOne

     

    and use it as a filter for the visual setting it to "is 1."

     

    Best
    D

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Wow, Thank you a lot for the quick respose. I will try it tomorrow in the morning and will let you know if I could fix it. Thank you 🙂
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous,

       

      thank you again for your help yesterday evenig. The problem is, that I have several Assetnames called Festival in a country, so it would not help a lot when I add the Country code. If I will change to Top 10, then I will have the same problem again.

       

      Unfortunately I am totally a beginner in writting measures or programming. I tried to copy the measure you wrote, but I dont know how to adjust the Table names with my data. 

       

      This Is my Data table:

       

       

       

      Thats how I tried to adjust it, but without results 😕

       

       

      Maybe you could explain me shortly, what I have to do? And what is meant by a FactTable, should I leave it or change the Name in the Measure?

       

      Thank you and best regards,

       

      Albert