Forum Discussion
SelectedValue producing odd results
I have a report detailing runners times some people have restricitons, i.e. parent with a child these records show as having an exception, Has Exception = Yes. I want to identify those with an exception, the table takes the form shown below:
| Runners Name | runners_id | Time | Has Exception | Text2 | Text3 |
| Runner 1 | 0095 | 75.92 | Yes | TRUE | FALSE |
| Runner 2 | 0096 | 19.8 | No | TRUE | FALSE |
The formulae are:
Text2 = IF(SELECTEDVALUE('dim_runner'[Has Exception]) = "No",FALSE(),TRUE())
Text3 = IF(SELECTEDVALUE('dim_staff'[Has Exception]) = "Yes",TRUE(),FALSE())
Runner 1 should show as having an exception and Runner 2 should not, but both formulae produce errors. Can you advise what I am doing wrong and how to solve this puzzle please.
In your example, I can see only one Has Exception column so I dont know which is which -whether that is from dim_runner or dim_staff. What I would do though is to create measures that returns the SELECTEDVALUE of those columns - they might be returning blanks or values other than what are expected - and then add those measures to the viz
HasException_Runner = SELECTEDVALUE ( 'dim_runner'[Has Exception] ) HasException_Staff = SELECTEDVALUE ( 'dim_staff'[Has Exception] )Plese note that if SELECTEDVALUE sees more than one distinct value in the current row context, it will return blank.
11 Replies
Hi Mark_A12
If you are creating a calculated column then instead of using SELECTEVALUE you should use IF function.Text2 = IF('Data'[Has Exception] = "No", FALSE(), TRUE())Text3 = IF('Data'[Has Exception]= "Yes", TRUE(), FALSE())If this answers your questions, kindly accept it as a solution and give kudos.
Thank you very much, I'm afraid I didn't make myself clear. I am trying to add a flag to the name column based on the Has Exception field but using Text2 incorrectly adds the flag to Runner 2 and Text 3 fails to add the flag to Runner 1.
Hi Mark_A12
By the looks of it, those measures should return either TRUE/FALSE but not an error so I am wondering what errors you're encountering. That aside, your measures could be simplified as:
HasException_Runner = -- Returns TRUE if 'Has Exception' is not "No" SELECTEDVALUE ( 'dim_runner'[Has Exception] ) <> "No" HasException_Staff = -- Returns TRUE if 'Has Exception' is "Yes" SELECTEDVALUE ( 'dim_staff'[Has Exception] ) = "Yes"Hi danextian, thank you for your advice, the error is they are incorrectly identifying if a runner has an exception. Text2 identifies Runner 2 as having an exception, whereas Text3 incorrectly identifies Runner 1 as not having an exception.
In your example, I can see only one Has Exception column so I dont know which is which -whether that is from dim_runner or dim_staff. What I would do though is to create measures that returns the SELECTEDVALUE of those columns - they might be returning blanks or values other than what are expected - and then add those measures to the viz
HasException_Runner = SELECTEDVALUE ( 'dim_runner'[Has Exception] ) HasException_Staff = SELECTEDVALUE ( 'dim_staff'[Has Exception] )Plese note that if SELECTEDVALUE sees more than one distinct value in the current row context, it will return blank.
- TEXT2 = if('DATA'[Has Exception]="YES",TRUE(),FALSE())
Thank you all for your help. I have found an error relating to a linked table that is causing multiple values in the Has Exception field so SelectedValues is returning a blank. I will need to work out where this iem is used and rebuild the model.
Hi Mark_A12 ,
Just checking that you mentioned that you found the issue was due to a linked table causing multiple values in the Has Exception field, and that you were planning to rebuild the model.
Were you able to get that sorted out? If yes, we’d love to hear how you resolved it, it could help others who might run into a similar challenge.
Also, huge thanks again to all the super users mdaatifraza5556 , Rahul_2645 and danextian . Looking forward to your update.
Thanks,
Akhil.Hi Mark_A12 ,
Hope everything's going smoothly on your side. Just wanted to follow up and see if you were able to make progress after identifying that the linked table was causing multiple values in the Has Exception field.
Regards,
AkhilHi Mark_A12 ,
Just circling back one last time. I remember you had identified that the issue was due to a linked table returning multiple values in the Has Exception field, and you were considering rebuilding the model to fix it. Were you able to get everything working smoothly? If so, would happy to hear how you tackled it could be really helpful for others facing something similar. And of course, if you're still working through it or hit a roadblock, happy to help wherever i can.
Regards,
Akhil.Hi Mark_A12 ,
Just checking in one last time haven’t seen you back in a while. As per the Microsoft Fabric Community Forum guidelines, we’ll go ahead and close this thread for now. If your issue has already been resolved, that’s great to hear.
But if you still need help down or anything else feel free to create a new post anytime. The community is always here to support you.
Regards,
Akhil.
