cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ironpixel
Microsoft
Microsoft

when data is text and visual isBlank, how can I display alternate text

Hello,
I have a measure that allows me to show alternate text in a visual when data in a column is numeric and the visual isBlank.

 

That being said, when the data is text and the visual shows (Blank) I can't use the same formula.

 

I have been trying a number of things, and looking around, but haven't found a solution yet.
Any help would be greatly appreciated!

A little more detail here:  My table might have multiple values where a card will show (Blank) if none of those values are present after filtering from other slicers.

 

Example:

The Card will show (Blank) if a slicer is asking to show the count of b, but it will show 4 if the slicer is asking for the count of a.

I would like the card to show some other text.

State

a

a

c

a

c

a

Again, Much Appreciated!

1 ACCEPTED SOLUTION
JpCon2
Frequent Visitor

Well, I think I'm following you now. Therefore, you want a count of the text values, but if you select a value in the slicer that does not exist, you want an alternative value. I assume you have a separate table that you are using to populate the slicer and that it has a relationship between that table and the State table.

I would use this measurement for the visual card:

Card: IF ( ISBLANK (COUNT (Status)), "Alternative Text", COUNT (Status ))

So if your Slicer has a, b, c, d and your State table has a, a, c, a, c, a... and there's a relationship between them. Then, when you select one in the slicer, you'll see 4 on the card. If you select b, you will see "Alternative Text".

View solution in original post

6 REPLIES 6
JpCon2
Frequent Visitor

Well, I think I'm following you now. Therefore, you want a count of the text values, but if you select a value in the slicer that does not exist, you want an alternative value. I assume you have a separate table that you are using to populate the slicer and that it has a relationship between that table and the State table.

I would use this measurement for the visual card:

Card: IF ( ISBLANK (COUNT (Status)), "Alternative Text", COUNT (Status ))

So if your Slicer has a, b, c, d and your State table has a, a, c, a, c, a... and there's a relationship between them. Then, when you select one in the slicer, you'll see 4 on the card. If you select b, you will see "Alternative Text".

View solution in original post

Hi @JpCon2

Your suggestion worked when my column had text and I wanted to replace it with something other than (Blank).. Thank you!

Card = IF ( ISBLANK (COUNT (Status)), "Alternative Text", COUNT (Status ))

When my data had numbers in it and I needed an average of those numbers I ended up writing two measures...

Card Average = AVERAGEX ( FILTER ( 'Table', 'Table'[Value] <> BLANK () ), 'Table'[Value] )
Card Average (alternate text) = if(ISBLANK([Card Average]), "alternate text", [Card Average] )
Thanks for the help!!
V-lianl-msft
Community Support
Community Support

Hi @Ironpixel ,

 

Try to create the measure.

Measure 2 = IF(LEN(MAX('Table (2)'[Column1]))=0,"blank",MAX('Table (2)'[Column1]))

test_when data is text and visual isBlank, how can I display alternate text.PNG

 

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

Hi @V-lianl-msft 

 

I edited the formula to fit my scenario and changed the text, yet I am still showing (Blank) vs the alternate text I put in.

I will add a post with more detail to my scenario..

Thanks!

JpCon2
Frequent Visitor

If I'm reading your question correctly, you have a column with text values in it.  You then have a visual that will display a value from that column.  Since you can't show an individual field, you will need the measure to aggregate the text value.  Try using ALLSELECTED().  Let's say you wanted to replace the BLANK() with "Unknown", then you could use:

 

IF(ISBlank(ALLSELECTED('Table'[Coulmn])), "Unknown", ALLSELECTED('Table'[Column]))

 

Use the measure in the visual.  This measure will work in a card visual.

Hi @JpCon2 
I tried that but unfortunately I am still getting an error:

A table of multiple values was supplied where a single value was expected.

 

I will add more detail to my orginal post to see if that helps.

Thanks!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!