Forum Discussion
Select three different columns using measure
Hello everyone,
I'm studying measures through a classic rock top500 ranking which I found on Kaggle.
For my purpose I intend to select three different columns: Music, YearRanking, Top500.
Music contains all the music from this database;
YearRanking contains all the years, from 2015 to 2022, that the interview was applied;
Top500 contains a ranking that goes from 0 to 500, where 0 means that nobody rated that song and 500 means that this song was rated, but it's in the last position.
Ok, so the problem I'm facing is:
I want to filter this 3 columns to find the 5 most rated song, from 1 to 5, where ALL these songs must be presents in 2015, 2016, 2017, 2018, 2019, 2020, 2021 AND 2022.
How can I solve that?
I tried to get the closest I can, using this measure:
TopMusic = CALCULATE(COUNTROWS(classic_rock_playlist), classic_rock_playlist[Top500]<>0, classic_rock_playlist]<6)
But when I use this as a solution, the logic applied here is: Find the songs that is rated from 1 to 5 in any year from 2015 to 2022.
But what I want is to find the songs that is rated from 1 to 5 in all the years.
Thanks in advance.
DanielaAmadeuPr It's not a trivial problem to solve. Your filter clause is the problem with your CALCULATE and you really shouldn't be using CALCULATE as a beginner. CALCULATE is an incredibly complex function. With your filter clause you are always going to be in the position of getting music that appears in the top 5 for one or more years but not all years. That's the problem you have to solve for. So, to explain the code:
TopMusicTable = /* First, get a table for all music that appears in the top 5 and group that by Year and a unique Key in this case since there are multiple songs named One. The Key is simply a concatenation of artist and music with a | character in between. Use TOCSV(__Table) in the RETURN to visualize the table that is returned. You can use a Card visual for this. */ VAR __Table = SUMMARIZE( FILTER('classic_rock_playlist',[Top500]<>0 && [Top500]<6), [YearRanking], [Key] ) /* This simply counts the distinct Year values so now you know how many years you are dealing with */ VAR __NumYears = COUNTROWS(DISTINCT('classic_rock_playlist'[YearRanking])) /* /* Next, we add a column called __NumYears to our base table. This column calculates how times that music appears in the base table (__Table). It does this by getting the Key adn then counting the rows in the base table where the Key matches. Again, use TOCSV(__Table1) as the RETURN value to visualize this table in a Card visual for example. */ VAR __Table1 = ADDCOLUMNS(__Table, "__NumYears", VAR __Music = [Key] VAR __Result = COUNTROWS(FILTER(__Table,[Key] = __Music)) RETURN __Result ) /* Now all we have to do is to filter the table with the additional column (__Table1) where the __NumYears column matches our __NumYears VAR that we created, meaning that the song appeared in all years because they match. We only want the key column as a return value so we use SELEECTCOLUMNS for that and just we only want distinct values so we use DISTINCT as well. */ VAR __Result = DISTINCT(SELECTCOLUMNS(FILTER(__Table1, [__NumYears] = __NumYears),"Key",[Key])) RETURN __Result
18 Replies
what context do you have for the measure?
try like:
TopMusic = CALCULATE( COUNTROWS(classic_rock_playlist), classic_rock_playlist[Top500]<>0, classic_rock_playlist[Top500]<6, classic_rock_playlist[YearRanking]>=2015, classic_rock_playlist[YearRanking]<=2022 )Hello FreemanZ ,
I was trying exactly this as a solution. But the return I get is the same: I can see all the musics ranking from 1 to 5 that are in one year or another.Thanks.
DanielaAmadeuPr I'm thinking something like this:
Measure = VAR __Table = SUMMARIZE( FILTER('classic_rock_playlist',[Top500]>0 && [Top500]<6) [YearRanking], [Music] ) VAR __NumYears = COUNTROWS(DISTINCT('classic_rock_playlist'[YearRanking])) VAR __Table1 = ADDCOLUMNS( __Table, "__NumYears" = VAR __Music = [Music] VAR __Result = COUNTROWS(FILTER(__Table,[Music] = __Music)) RETURN __Result ) VAR __Result = COUNTROWS(FILTER(__Table1, [__NumYears] = __NumYears)) RETURN __ResultHello, Greg_Deckler
First of all, thanks for helping me.
I tried your code as a solution, but it's only filtering the musics that are in some of the years.
Here is your code:TopMusic = VAR __Table = SUMMARIZE( FILTER('classic_rock_playlist',[Top500]<>0 && [Top500]<6), [YearRanking], [Music] ) VAR __NumYears = COUNTROWS(DISTINCT('classic_rock_playlist'[YearRanking])) VAR __Table1 = ADDCOLUMNS(__Table, "NumYears", VAR __Music = [Music] VAR __Result = COUNTROWS(FILTER(__Table,[Music] = __Music)) RETURN __Result ) VAR __Result = COUNTROWS(FILTER(__Table1, __NumYears = __NumYears)) RETURN __Result
And here is the bar chart:
As you can see in the purple bar, it's a value that occurs only in 2022, but it's present when it should not be present.
😞DanielaAmadeuPr Yeah, kind of flying blind without the dataset, is it this one?
Classic Rock - Top 500 songs | Kaggle
If not, which dataset are you using?
