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 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
Hello Greg_Deckler
I was wondering... if I can't use CALCULATE as a beginner, what should I use instead?
Thanks
DanielaAmadeuPr Use this approach (below). Also, my new channel DAX For Humans is all about this technique although it's very early in the series so far.
With your table solution, I now have a new problem: I need to filter the Blank songs generated by your table. I don't see it as an effective solution.
You could do this, updated PBIX see page 2 attached.
Rankings Measure = VAR __Table = SUMMARIZE( FILTER(ALL('classic_rock_playlist'),[Top500]<>0 && [Top500]<6), [YearRanking], [Key] ) VAR __NumYears = COUNTROWS(DISTINCT(ALL('classic_rock_playlist'[YearRanking]))) VAR __Table1 = ADDCOLUMNS(__Table, "__NumYears", VAR __Music = [Key] VAR __Result = COUNTROWS(FILTER(__Table,[Key] = __Music)) RETURN __Result ) VAR __TopTable = DISTINCT(SELECTCOLUMNS(FILTER(__Table1, [__NumYears] = __NumYears),"Key",[Key])) VAR __Key = MAX('classic_rock_playlist'[Key]) VAR __Result = IF(__Key IN SELECTCOLUMNS(__TopTable,"__Key",[Key]),MAX('classic_rock_playlist'[Top500]), BLANK()) RETURN __Result