topic Re: Filter TOP N without duplicate in DAX Commands and Tips

Filter TOP N without duplicate

Anonymous — Sun, 19 Apr 2020 20:33:06 GMT

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!

Re: Filter TOP N without duplicate

Anonymous — Sun, 19 Apr 2020 21:39:28 GMT

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

Re: Filter TOP N without duplicate

Anonymous — Sun, 19 Apr 2020 23:26:39 GMT

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 🙂

Re: Filter TOP N without duplicate

Anonymous — Mon, 20 Apr 2020 19:35:53 GMT

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

Re: Filter TOP N without duplicate

Anonymous — Mon, 20 Apr 2020 20:25:31 GMT

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-month-to-date

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