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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ironpixel
Employee
Employee

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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