Forum Discussion
Unable to get selectedvalue
Hey Experts,
I have a table having column named Country and I want the score of the country who played against which user has selected from slicer. For instance, when user select Canada from from slicer then graph should shows the score of PAK, IRE, and USA.
so, when I getting value from SELECTEDVALUE and using that in calculating average score, I am getting nothing. Below, is my code. I would appriciate if you have other approach to solve this, please share with us.
AVERAGEX( Filter(Table, Country <> SELECTEDVALUE), Score)
For your reference.
Step 0: I use these data below.
Step 1: I make a measure.
M_SUM_ContainSt = SUMX(Filter('DATA','DATA'[Country]<>[M_SELECTEDVALUE]&&CONTAINSSTRING('DATA'[match_id],[M_SELECTEDVALUE])),'DATA'[Score])
Step 2: I make a matrix.
- CAN -
- IRE -
san_jayaswal,
I've attached my version of the solution that solves both your original problem and this new problem. I do require that there is a Countries dimension table, but in my solution, there is a one-to-many relationship between Countries and your original fact table. This way, all your normal measures should still work, while still meeting your original requirements.
For CAN:
For PAK:
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
13 Replies
Wilson_Memorable Member
Hi san_jayaswal,
I was able to calculate opponent's average score with this measure:
Opp Avg Score = VAR SelCountry = SELECTEDVALUE ( 'Table'[Country] ) -- the selected country from the slicer VAR Matches = VALUES ( 'Table'[match_id] ) -- the list of all the matches played by the country VAR OpponentScores = CALCULATETABLE ( VALUES ( 'Table'[Score] ), -- all the scores REMOVEFILTERS ( 'Table' ), -- of all the teams Matches, -- in matches played by the selected country 'Table'[Country] <> SelCountry -- that were not the selected country ) VAR Result = AVERAGEX ( OpponentScores, 'Table'[Score] ) RETURN Result(By the way, your syntax for SELECTEDVALUE is incorrect. This is the documentation for the function. This is a great article by SQLBI about how to use SELECTEDVALUE.)
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Dear Wilson_, but just you try to select CAN from slicer your table will return only score of CAN, although I am looking the country's score who played against CAN (user selected from slicer).
- mickey64
Super User
Step 0: I use your data.
Step 1: I make a 'Sel_Country' table and make a slicer with it.
Sel_Country = SUMMARIZE('DATA','DATA'[Country])
Step 2: I make 2 measures.
M_SELECTEDVALUE = SELECTEDVALUE('Sel_Country'[Country])
M_AVG = AVERAGEX(Filter('DATA',
'DATA'[Country]<>[M_SELECTEDVALUE]),'DATA'[Score])
Step 3: I make a card visual.
Sorry, you did not understand my concern. If you take column chart and bring Country and Score column and if user select CAN and this graph should display IRE, USA, and PAK with their score. I hope I would be able to understand you. I know this is quite complex situation but this is what I am looking. Please help
- mickey64
For your reference.
Step 0: I use these data below.
Step 1: I make a measure.
M_SUM_ContainSt = SUMX(Filter('DATA','DATA'[Country]<>[M_SELECTEDVALUE]&&CONTAINSSTRING('DATA'[match_id],[M_SELECTEDVALUE])),'DATA'[Score])
Step 2: I make a matrix.
- CAN -
- IRE -
