cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ADiBo Frequent Visitor
Frequent Visitor

How to make a card return predetermined text when no/multiple items in a category selected (example)

https://www.timesunion.com/local/article/More-Capital-Region-students-are-qualifying-for-12497790.ph...

 

You can see the example a bit down the page under "Free Lunches..."

 

When no counties are selected or more than one, the card at the top returns "Multiple Counties". How is this being accomplished? I've seen asked and answered examples of returning blanks, but not returning text such as this.

 

Based on this discussion (https://community.powerbi.com/t5/Desktop/Make-visual-blank-display-instructions-until-other-visual/t...) talking about another aspect of the same report, I was able to add this measure. 

Show Text = IF(ISFILTERED(MedianHouseIncome[County]),"","Select County") This currently makes it so that when no County is selected, it gives the desired text. What can I add to this to make it also return text when 2 or more Counties are selected?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
dedelman_clng New Contributor
New Contributor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

Try this code:

 

Income Show Text =
IF (
    NOT ( ISFILTERED ( MedianHouseIncome[Geography] ) ),  //No filters
    "Select County",
    IF (
        COUNTROWS ( FILTERS ( MedianHouseIncome[Geography] ) ) > 1, //More than one value
        "Multiple Counties",
        SELECTEDVALUE ( MedianHouseIncome[Geography] ) //else condition, implies 1
    )
)

 

Hope this helps

David

View solution in original post

6 REPLIES 6
dedelman_clng New Contributor
New Contributor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

Try HASONEVALUE

 

IF ( HASONEVALUE ([County]), [County], "Multiple Counties" )

Hope this helps

David

ADiBo Frequent Visitor
Frequent Visitor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

Thanks a lot, really helped out! I've almost got it, here's the full code I have right now.

 

Income Show Text = IF(ISFILTERED(MedianHouseIncome[Geography]),"", "Select County") & IF(HASONEFILTER(MedianHouseIncome[Geography]),FILTERS(MedianHouseIncome[Geography]),"") & IF (HASONEVALUE (MedianHouseIncome[Geography]), "" , "Multiple Counties" )
 
It works when either one county is selected or when multiple are selected (returning county name and "Multiple Counties" respectively. However, when none are selected, it says "Select CountyMultiple Counties". Any ideas for getting around that?
ADiBo Frequent Visitor
Frequent Visitor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

Okay so after thinking about it more, I see what's happening. The & is a concatenation, so it's putting the Select County and Multiple Counties together because they're both false. When multiple counties are selected it shows up correctly because only one is false.

 

As a work around I could separate them out into two measures and overlay the cards, but I would really like to avoid that if possible and keep it in one measure.

Highlighted
dedelman_clng New Contributor
New Contributor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

Try this code:

 

Income Show Text =
IF (
    NOT ( ISFILTERED ( MedianHouseIncome[Geography] ) ),  //No filters
    "Select County",
    IF (
        COUNTROWS ( FILTERS ( MedianHouseIncome[Geography] ) ) > 1, //More than one value
        "Multiple Counties",
        SELECTEDVALUE ( MedianHouseIncome[Geography] ) //else condition, implies 1
    )
)

 

Hope this helps

David

View solution in original post

ADiBo Frequent Visitor
Frequent Visitor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

That worked! David, you are a rock star!

dedelman_clng New Contributor
New Contributor

Re: How to make a card return predetermined text when no/multiple items in a category selected (exam

One further thing to realize is that if there is any other filter anywhere in the report or page (not just on the visual or on a specific slicer) then ISFILTERED is going to return TRUE

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 356 members 3,058 guests
Please welcome our newest community members: