Forum Discussion
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)...
- 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
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
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 🙂