Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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?
1 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 

 

Untitled.png 

 

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
AnthonyTilley
Solution Sage
Solution Sage

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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:

Explanation1.png

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

 

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.

filter.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

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 

 

Untitled.png 

 

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors