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
CookieMonster
Helper I
Helper I

Do not show data in Table and Multi-Row Card when Text Filter is empty

I have a Text Filter, a table and a Multi-row Card.

 

When I search in the Text Filter, only the relevent data is shown in the table and Multi-row Card, but when I clear the Text Filter, all data is shown in both.

I would like it so that when I clear the Text Filter, the table and Multi-row Card is empty, how can I achieve this?

1 ACCEPTED SOLUTION

1. Create a measure

 

MeasureToCheck = COUNTROWS(ALLSELECTED(Sheet1[AnimalType]))

 

2. Select table visual first and then click and drag that measure to visual level filter (see pic below)

 

Capture.JPG

 

and choose is 1.

 

3. Repeat 2 but first click row card.

 

So we have

 

 Capture1.JPG

 

then

 

Capture2.JPG

 

and at the end

 

Capture3.JPG

 

Are you ok???

 

 

 

 

 

 

Are you ok with this???

View solution in original post

14 REPLIES 14
sokg
Solution Supplier
Solution Supplier

Try SELECTEDVALUE

 

 

can you show us the measures used in Multi-row Card and which column use in Text Filter????

Sorry im new to Power BI, can you expand on what you mean by try SELECTEDVALUE?

 

And what do you mean by 'measures used in Multi-row Card and which column use in Text Filter', do you mean what fields and filters?

You should use SELECTEDVALUE in all those cases when you need to read a single value selected in the filter context, obtaining blank or another default value in all other cases.

 

More info here

 

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

 

 

Yes show fields and filters

Text Filter is using the Field: AnimalType

Multi-row Card is has the filter: AnimalType (All)

 

And when I type in the Text Filter, it does filter the Multi-Row Card as expected, so that works fine, its just getting it to show nothing in Multi-row Card when the Text Filter is blank, im looking into that link now, just seems confusing to me until I learn and understand DAX and how to access it in Power BI

Can you provide us with sample data???

Unfortuently not, I could put some sample data together in excel as the source and alter my BI report to use that until I work out how to do this?

 

I have tried creating a new measure, which I set the DAX to 'Measure = SELECTEDVALUE ( T1[AnaimalType] )', and applied it as a filter to the Multi-row Card, but this hasnt achieved what im after, im guessing im using it wrong.

Unfortuently not, I could put some sample data together in excel as the source and alter my BI report to use that until I work out how to do this?  

 

Yes do that.

 

and create this measure and put it in your row card not in visual level filter

Measure = SELECTEDVALUE ( T1[AnaimalType] ; <your measure here to show if one animal type is selected>)

Examples can be downloaded from:

RawData Excel Sheet > https://drive.google.com/open?id=1t99ftzHDmf2DFU8eHIg6laL8UGDxUlyr

Power BI Report > https://drive.google.com/open?id=10WiaUl5iwxFD_cdGtrf0WMNrCLeLHScU

 

So...

 

In the Text Filter, if you type in Dog, only Dog's should show in table and multi-row card

In the Text Filter, if you type in Cat, only Cat's should show in table and multi-row card

If Text Filter is empty, then table and multi-row card should not show any data.

1. Create a measure

 

MeasureToCheck = COUNTROWS(ALLSELECTED(Sheet1[AnimalType]))

 

2. Select table visual first and then click and drag that measure to visual level filter (see pic below)

 

Capture.JPG

 

and choose is 1.

 

3. Repeat 2 but first click row card.

 

So we have

 

 Capture1.JPG

 

then

 

Capture2.JPG

 

and at the end

 

Capture3.JPG

 

Are you ok???

 

 

 

 

 

 

Are you ok with this???

Just wanted to say thanks for this solution. Whilst my scenario was slightly different it did help me to get on the right track. I had a 100% Stacked Graph visual and a multi-row card. I only wanted to show data in the card when a specific column of the Graph was selected.

I adapted the solution, checking that the count returned by the Measure = 1 or greater than 1. When the count is greater than 1 set all the elements of the card to match the background colour, when the count is 1 then set the colours as required to display.

 

This was for a Service Health dashboard requirement, dynamically populating a traffic light status indicator per application and displaying the underlying metrics when selected.

this is an awesome idea! comparing to selectvalue

Thanks!!! it was really helpfull, my head almost explotes trying to filter my pane!!

 

Gracias! Casi colapso!

Yes this works perfectly and I understand how this works thanks.

Im wondering if your understanding my question, SELECTEDVALUE looks to be to put in a default value if a cell is blank, otherwise use the cell value.

 

But this is not what im after, can you confirm if my orgional question makes sense?

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.