Filter TOP N without duplicate

[Anonymous]

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!

[Anonymous]

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]

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

[Anonymous · Accepted solution]

If you are a total beginner... then you should first learn about the subject a bit and then try your hand at it. Here's something to get you started:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

You should never use a single table to hold all of your data unless... you want to create a monster you won't be able to control and unless you want to produce wrong numbers in no time. Also, if the model is incorrect, DAX will be extremely hard to write, will be complex and inefficient. So, please do yourself a favour and learn first how to build correct and simple models. Start with the above.

DAX and PBI are not Excel and DAX requires a lot of learning to use it with confidence and correctly. Please, therefore, first learn the foundations and then start building models. Not the other way round. That's the best advice I can give you.

The best site about DAX is www.sqlbi.com. But there are others that do just as well. For instance, Matt Allington's. Have a look at this

https://www.youtube.com/watch?v=eOJkOWMYSkk

and you can then research further yourself. There are many good materials about DAX and PBI on YT. Please use them to your advantage.

Best
D

[Anonymous]

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 🙂