Unable to get selectedvalue

[san_jayaswal]

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)

 

[mickey64 · Accepted solution]

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 -

[mickey64]

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.

[san_jayaswal]

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 · Accepted solution]

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]

What ever your solution worked, but anyway this end up with another issue as now I have to mention two country slicers, the one who is connected with all other graphs and another to solve this purpose. Because, if I am keeping only this country slicer then I have to adujst in every DAX function, such as if I want to get score of all the country then DAX function would be 

Average Score = AVERAGEX(FILTER(DATA, DATA[Country] = SELECTEDVALUE(Sel_Country[Country])), DATA[Score]). But again there is one issue, this will be blank until and unless I select the contry name from the slicer. So, can you I achieve that shows all the country and filter when I select from slicer. Can you provide me solution?

[sivarajan21]

Thanks @mickey64 

Hi @san_jayaswal ,

If your question is answered, please can you accept this as a solution and help the community 

Thanks in advance!

[san_jayaswal]

What ever your solution worked, but anyway this end up with another issue as now I have to mention two country slicers, the one who is connected with all other graphs and another to solve this purpose. Because, if I am keeping only this country slicer then I have to adujst in every DAX function, such as if I want to get score of all the country then DAX function would be 

Average Score = AVERAGEX(FILTER(DATA, DATA[Country] = SELECTEDVALUE(Sel_Country[Country])), DATA[Score]). But again there is one issue, this will be blank until and unless I select the contry name from the slicer. So, can you I achieve that shows all the country and filter when I select from slicer. Can you provide me solution?

[Wilson_]

san_jayaswal,

Unless I'm misunderstanding your question, you don't need to use SELECTEDVALUE here.

You can just do Average Score = AVERAGE ( DATA[Score] ).

[san_jayaswal]

Yeah, but if user select any particular country from slicer then chart won't filter because the solution he has given of earlier question was creating another Country table that was not having relationship with Data table. So, can I achieve both using one slicer. I hope, you understand. Also, I would like to request you to run his code from your end then grab Column chart and create a avarage score measure for country and then try to filter using the same slicer. And you will notice it won't work. 

[Wilson_ · Accepted solution]

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.

[san_jayaswal]

Amazing bro, I appriciate you efforts and knowledge, finally I got the desire output that I am looking for. 

[Wilson_]

You're welcome, happy to help! 😄

[Wilson_]

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.

[san_jayaswal]

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).