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

[gp10]

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 1	Column 2	Column 3
text11	text21
	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.

[gp10 · Accepted solution]

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]

Hi @gp10 , 

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

Unipovt the columns.

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

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

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.

[gp10]

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 1	Column 2	Column 3
text11	text21
	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]

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.

[gp10]

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.

[DvdH]

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.

[gp10]

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 

[DvdH]

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

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

[gp10]

Hi @DvdH ,
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 1	Column 2	Column 3
text11	text21
	text22
text13		text33
		text34

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