Forum Discussion
Select three different columns using measure
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
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?
Yes!
DanielaAmadeuPr OK, here you go. PBIX is attached below signature.
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(DISTINCT(SELECTCOLUMNS(FILTER(__Table1, [__NumYears] = __NumYears),"__Music",[Music]))) RETURN __ResultHey, Greg_Deckler
Thanks again for helping me.
The requirements is:
The values of the ranking must be from 1 to 5;
The songs that are from 1 to 5 must be present in all years from 2015 until 2022.
There are only three songs that follow those rules:
Stairway to heaven, One, and Bohemian Rhapsody.
Thanks again!!!DanielaAmadeuPr Right, the PBIX file does exactly that and comes out with an answer of 3. You can even revise the measure to CONCATENATEX them up so you get the actual songs back. Here's the CONCATENATEX version:
TopMusic2 = 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 = CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(__Table1, [__NumYears] = __NumYears),"__Music",[Music])),[__Music],",") RETURN __Result
Hello, Greg_Deckler
Apologies for the delay.
I went back to studying the problem, and came very close to the solution. My only impediment has been replacing SELECTEDVALUE for another measure that fits better.Here's my new measure:
TopMusic =CALCULATE(SELECTEDVALUE(classic_rock_playlist[Top500]), classic_rock_playlist[Top500]<6 ,classic_rock_playlist[YearRanking]="2015")The result:As you can see, if one value is present in Top500, it repeats in all the years that follows. But there's only three Top500 that repeats in the following years. So I really believe that my problem is the SELECTEDVALUE, because it returns only one value.DanielaAmadeuPr I don't think that SELECTEDVALUE is your issue. See updated PBIX file that presents your visual. PBIX is attached below signature.