Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello experts,
I have a table like that:
Player Name | Year | Market Value |
Player 1 | 2014 | 10 |
Player 2 | 2014 | 15 |
Player 1 | 2015 | 20 |
Player 2 | 2015 | 22 |
Player 3 | 2015 | 25 |
Player 1 | 2016 | 20 |
Player 2 | 2016 | 25 |
Player 3 | 2016 | 15 |
* please note that there is no player 3 in year 2014.
I have got a Measure that returns the "Player Name" corresponding to the highest Market Value:
TopPlayer =
MAXX (
TOPN (
1,
VALUES ( 'transfermrkt'[Player] ),
CALCULATE ( MAX ( 'transfermrkt'[Mrkt value] ) ), DESC
),
[Player]
)
and I want to create a Line Plot which plots the trend of Market Value over Years of the player that results from the above measure.
This is an example of the output I wish:
Immagine.png (470×398) (ibb.co)
but here the output is correct because I used "Player Name" as legend and manually selected "Kalidou Koulibaly". However, as I said before, I would like to retrieve the "Player Name" from the above Measure.
Can anybody suggest me a way to do that?
Thanks
Solved! Go to Solution.
Yep. You can either filter or not filter the visual with the slicer but not both at the same time.
The way to get around this is to have the slicer be from a table unrelated to the table you use for the x-axis so that you can filter one independently of the other. I usually do this by defining a new table (with no relationships to other tables) for the slicer so you can use the selected slicer value as a parameter rather than directly filtering.
Once you do this, you need to update your measure accordingly. Something like this
TopPlayerMarketValue =
VAR TopPlayer =
CALCULATE (
[TopPlayer],
FILTER (
ALL ( transfermrkt ),
transfermrkt[Year] = SELECTEDVALUE ( Slicer[Year] )
)
)
RETURN
CALCULATE (
SUM ( transfermrkt[Mrkt value] ),
KEEPFILTERS ( transfermrkt[Player] = TopPlayer )
)
You can write a measure that only returns nonblank values for the top player.
TopPlayerMarketValue =
VAR TopPlayer = CALCULATE ( [TopPlayer], ALL ( transfermrkt ) )
RETURN
CALCULATE (
SUM ( transfermrkt[Mrkt value] ),
KEEPFILTERS ( transfermrkt[Player] = TopPlayer )
)
In the above, I have to use ALL or else it will take the top player within the Year and Player filter context from the chart visual.
Thanks for your help.
Unfortunately, the solution does not suit my needs. I would like the line chart to automatically refer to the Market Values of the Player returned by the measure I posted above, that is, the TopPlayer of a particular year. For instance, in the sample table above, if I filter by year 2014 the TopPlayer is Player2 whereas if I filter by 2015 TopPlayer is Player3. So in the first case I want the line chart to be referred to Player2 whereas I want it to be referred to Player3 in the latter case.
I will clarify with some pics:
Immagine1.png (454×433) (ibb.co)
Here it is okay becasue the overall TopPlayer returned by your measure coincides with the TopPlayer of the selected season whose name, Kalidou Koulibaly, is returned by the measure TopPlayer.
But, when I filter by another season...
Immagine2.png (459×433) (ibb.co)
...the chart still refers to the previous player whereas I would like to have the same plot referred to the new TopPlayer, that is, Gonzalo Higuain.
Try using ALLSELECTED rather than ALL in the measure.
Thanks again.
It seems we are almost there.
Now it correctly selects the player if I keep the interaction with the filter by season, but of course it shows only one point relative to the selected year:
https://i.imgur.com/qyodyFW.png
If I delete the interaction with the filter, it does not select the TopPlayer for that year whereas it returns the overall TopPlayer, that is, always the same for every season.
Yep. You can either filter or not filter the visual with the slicer but not both at the same time.
The way to get around this is to have the slicer be from a table unrelated to the table you use for the x-axis so that you can filter one independently of the other. I usually do this by defining a new table (with no relationships to other tables) for the slicer so you can use the selected slicer value as a parameter rather than directly filtering.
Once you do this, you need to update your measure accordingly. Something like this
TopPlayerMarketValue =
VAR TopPlayer =
CALCULATE (
[TopPlayer],
FILTER (
ALL ( transfermrkt ),
transfermrkt[Year] = SELECTEDVALUE ( Slicer[Year] )
)
)
RETURN
CALCULATE (
SUM ( transfermrkt[Mrkt value] ),
KEEPFILTERS ( transfermrkt[Player] = TopPlayer )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |