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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gp10
Advocate III
Advocate III

Measure to show only non blank values in a table visual when a column is selected in a slicer

Hi community,


I have a table visual.

 

What I would like is to have a measure and  I do not want to unpivot the original table.
The result should be one column without blanks only if one column is selected in the slicer.

So the table visual is:

Column 1Column 2Column 3
text11text21 
 text22 
text13 text33
  text34


When nothing is selected we see the above visual.
If we select all we see the above visual.
If we select Column 1 we see this:

Column 1
text11
text13


If we select Column 2 we see this:

Column 2
text21
text22


If we select Column 3 we see this:

Column 3
text33
text34

 

etc


I can use Field Parameters and create a slicer to get only a single, but what is the best option to get the non-blank cells as well?

Thanks.

1 ACCEPTED SOLUTION
gp10
Advocate III
Advocate III

The measure I needed was this one:
Column 1 Measure =
CALCULATE (
SELECTEDVALUE ( Table[Column 1] ),
FILTER ( Table, NOT ( ISBLANK (Table[Column 1] ) ) )
)

Create such a measure for each column.
Then create a Field Parameter with those measures, add it in the table visual, and create a slicer with the parameter.
Thanks to everyone that helped here.

View solution in original post

9 REPLIES 9
gp10
Advocate III
Advocate III

The measure I needed was this one:
Column 1 Measure =
CALCULATE (
SELECTEDVALUE ( Table[Column 1] ),
FILTER ( Table, NOT ( ISBLANK (Table[Column 1] ) ) )
)

Create such a measure for each column.
Then create a Field Parameter with those measures, add it in the table visual, and create a slicer with the parameter.
Thanks to everyone that helped here.

v-rongtiep-msft
Community Support
Community Support

Hi @gp10 , 

I have created a simple sample, please refer to it to see if it helps you.

Unipovt the columns.

vpollymsft_0-1675733414302.png

Then create another table with the column 1, column 2 and column 3. And put it into the slicer.

vpollymsft_1-1675733468581.png

Finally create a measure.

 

Measure = var _1=IF(SELECTEDVALUE('Table (2)'[Column1])=SELECTEDVALUE('Table (3)'[Attribute]),MAX('Table (3)'[Value]),BLANK())
return
IF(_1<>BLANK(),1,IF(SELECTEDVALUE('Table (2)'[Column1])=BLANK(),1,BLANK()))

vpollymsft_2-1675733626590.pngvpollymsft_3-1675733637898.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-rongtiep-msft ,
thank you very much for your reply.

The thing is that I do not want to unpivot the original table. I do not want one column as the result. The result should be one column without blabnks only if one column is selected in the slicer.
So the table visual is:

Column 1Column 2Column 3
text11text21 
 text22 
text13 text33
  text34


When nothing is selected we see the above visual.
If we select all we see the above visual.
If we select Column 1 we see this:

Column 1
text11
text13


If we select Column 2 we see this:

Column 2
text21
text22


If we select Column 3 we see this:

Column 3
text33
text34

 

etc

jaweher899
Super User
Super User

You can create a measure to count the non-blank values in Column 1, and then use this measure in the filter to show only the non-blank cells. Here's one way to do this:

  1. Create a calculated column in your data table, with a formula to check if Column 1 is blank:

Non-Blank = IF(ISBLANK([Column 1]), 0, 1)

  1. Create a measure to count the non-blank values in Column 1:

Non-Blank Count = SUM(Data[Non-Blank])

 

  1. In the Power BI report, add the Non-Blank Count measure as a filter on the table visual, and set the filter to show only the values where Non-Blank Count > 0.

This will give you a table visual that shows only the non-blank values in Column 1, filtered by your selection in the slicer for Column 1.

Thanks @jaweher899 , its my fault that I havent made it clear but I dont want this just for column 1, but for every column. And only when I choose Column 1 in a slicer, like in a slicer created by Field Parameters, to able to see the only the selected columns with no blank values, otherwise when no filter is applied I want the original table.

Anonymous
Not applicable

So u want to only show a row of data if there are NO blanks?
Can u specify a bit more what u want? I don't think we get you.

If I choose Column 1 in the slicer I want to see the result I have in the original post, in a similar way if I choose Column 2, only column 2 and it's non-blank values. If nothing is selected in the slicer, the table visual remains as it is.
Thanks again for your time @jaweher899 

Anonymous
Not applicable

If solution below doesn't work simply use a filter on the visual? To show all and then unselect blank. See:

DvdH_0-1675755815852.png

Just select all then unclick the top one which is blank.

 

Hi @Anonymous ,
I have several columns. If I add this filter for each column in the table visual insead of what I have (and what I want when no filter is selected) I will have a blank table in my example, or I will be able to see only rows with no blank in any column.
If I do this in a table visual like this I will not get any columns as a result.

Column 1Column 2Column 3
text11text21 
 text22 
text13 text33
  text34

 

I have edited the original post to make it more clear. Thanks again.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.