Creating a measure displaying whether a value in row is selected or not

[Anonymous]

Hallo everyone, 

currently, I have a table consisting of two columns: A column of texts and a measure m I'm trying to create.

What I want is that every row, which contain the same text as the text of the selected row, has the value 1 in the measure and for every other row, there should be a 0.

I can get the text of the selected row by using the measure:

SelectedText = CALCULATE(SELECTEDVALUE(Table[TexttoCheck]; -9999); ALLSELECTED(Table))

(This means I receive a -9999 if I don't select one specific row.)

And by using the measure:

Measuretocreate = IF(VALUES(Table[TexttoCheck]) == "text1"; 1; 0)

Every row where each contains the text "text1" has the value 1, otherwise 0.

However, combining both measures won't work:

Measuretocreate = IF(VALUES(Table[texttoCheck])== [SelectedText]; 1; 0)

causes each row to contain the value 0 and it doesn't change whether any row is selected or not.

Has anyone a good idea?

[AnthonyTilley]

@Anonymous 

It may be better here to explain what you intend to use this colunm for.

the explination you gave would be impossable as when a measure is used in a table you cannot dynamically change the value of that measure based on a selected value within that table. 

When you use a SELECTEDVALUE function effectivly it says find the falue of my filters for this colunm if thers more than one then give me the default if theres is only one give me that value 

with in a table inorder to beable to select on a row by row basis that colunm must be included in the filters and will always have only one value in the given row. 

when you select the row in your table it filters all other visuals in the report page but it does not filter the orginal table, otherwise there would be no way to unfilter. imagin it this way iof you had a slicer with diffrent options and when you selected one value all the other values vanised then you would never be able to unfilter your slicer. 

so becuase the visual you are using to select the value is not filtered it will remain the same regardless of what is selected and therefore all rows will have there own unique value as the selected value.

sorry if this is confusing but in simple terms you cannotuse the SELECTEDVALUE function within a measure that will effect the same visual in which the selection is made.

If you let me know why you wanted to use this colunm then i may be able to provide an alternative solution

[Anonymous]

Dear @AnthonyTilley ,

thanks for helping me in my problem.

I'm attempting to highlight all texts where one has been selected. Currently, my table looks like this:

In this photo, I have selected the value "59200502". Due to the selection, I want to highlight all existing "59200502" using conditional formatting. 

I thought of using a measure with 0 and 1, but I would appreciate every other possible solution.

(Note: The column really contains text; the type can't be converted to number.)

[AnthonyTilley]

And What is the reason for Highlighting as opposed to filtering or sorting 

the highlighting is tricky and i cannot think of anyway that you could achive this within the same table 

Below are three alternatives using the slicer on the left the first table is filtered to only the given text and counts the number of instances of that text.

the 2nd shows each of the 3 rows with the same text and the thired does not use the filter and instead groups each of the rows by the text and then you can drill down. 

the specific function your after cannot be achived in anyway that i know of.

[Anonymous]

The thing is that the data should not be filtered when selecting a row. 

The reason for doing it is to find a pattern. 

The rows will be later sorted by a date and the same texts won't be next to each other. 

The goal is to select a text to highlight all the same texts and check whether the values between those highlighted texts repeat. So by using a slicer, there aren't any rows between the highlights which is why using the alternatives won't work.

[AnthonyTilley · Accepted solution]

If this is the reason then the only other method i can think of is to colour each line based on the text value.

as power bi does not allow conditional formating based on text only number then you would need to create a rank for each of the values. for example 

Measure = RANKX('table'[TEXTTOCHECK])

You can then use this Rank Measure in a conditional fomat using a rule and give each rank a diffrent colour.

this would not allow you to see based on a selected value but might still give the view you are looking for by looking at the colours between each of your choosen colour

This would look something like this 

 

There May be a way to conditionaly format the rows based on rank and a selected value in a slicer but im not sure i will explor this 

[Anonymous]

This could be a possible solution to my problem. Thanks for helping me out!