How to compare number of records displayed to number of table records?

[mbailey]

I have a report/dashboard that is shared with several users. The report has many slicers (filters) in it and some are long scrolling lists of customers, order numbers, etc.  I'm trying to come up with a way to visually indicate on the report if it's being Filtered or not since to my knowledge, there is not a built-in indicator.

An idea I had was comparing the number of records that would be displayed in an unfiltered view with the number being displayed with a filter applied. For example, unfiltered there are 1359 records displayed in my table grid. If I selected a particular customer and then only 895 records were displayed, I could test for "IF TotalRecords - DisplayedRecords >0". If the result was > 0, then I'd like to display a text message that says "FILTERED" somewhere on the report. I'm not even sure if you can conditionally display text or not?

Thanks,

Mike Bailey

[Vvelarde]

@mbailey

hello;

Create a measure and put in a Card Visual

Message-Filter = if (COUNTROWS(ALL(Table1))-COUNTROWS(Table1)>0;"Filtered","No Filter")

[mbailey]

Hi,

I put this in place and it doesn’t seem to work. When no filters are applied, it says Filtered, and when I apply a filter it still says Filtered. See below screen shots.

Message-Filter = if (COUNTROWS(ALL(DMTA_Service_Report))-COUNTROWS(DMTA_Service_Report)>0,"Filtered","No Filter")

This show the name of the table used in the measure, and the number of rows when Unfiltered:

Here you can see I’ve selected a Filter, and the number of rows has changed, but the Measure still says Filtered:

Thank you for your help!

Mike

[CheenuSing]

Hi @mbailey

You could try using HASONEVALUE to check if there are selections made in any slicer.

I see you have two slicers SRO Type and Stat. Use the expression as below

MessageFIlter =
                   If (HASONEVALUE(YourTable[SRO Type]) || HASONEVALUE(YourTable[Stat])
                                            ,"Filtered"
                                            , "Not Filtered")

HASONEVALUE helps to check if value in a slicer has been selected.

If this solves your issue please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[mbailey]

Hi CheenSing,

I'm am getting this error message when I try your formula:

     Function 'COUNTROWS' is not supported in this context in DirectQuery mode.

Here is the formula:

    MessageFIlter = If (HASONEVALUE(DMTA_Service_Material_Report[sro_type]) || HASONEVALUE(DMTA_Service_Report[Stat]),"Filtered", "Not Filtered")

Thanks,

Mike

[CheenuSing · Accepted solution]

@mbailey

Noted that you are using a Dirrect Query Mode and not Import Data.

To make the MessageFilterText to work properly, please follow the steps.

1. Clik on File 

2. Click Options and Settings

3. Choose Options

4. Under the Global column choose DirectQuery

5. Enable Allow Unrestricted measures in DIrectQueryMode.

6. Click OK

7. Save and exit

8. Open the PowerBI and open your pbix file , the MessageFilterText will work without giving error.

Just to add on,  the step 5 to enable the option,  will also allow some of the time inelligence to work.

If this solves your issue please accept this as solution and also give KUDOS.

Cheers

CheenuSIng 

[Sean]

@mbailey test with hardcoding your number like this...

Message-Filter = IF ( 1589 - COUNTROWS(DMTA_Service_Report)>0,"Filtered","No Filter")

what do you get with the above?

[Vvelarde]

@mbailey

If you have a filter (Report Filter, Page Filter or anything similar) it won't work.

For now; Other alternative is use IsFiltered Function but you need to put each column of yours slicers. 

Let me think if found other way.

[Sean]

@mbailey make sure you don't have ANYthing else pre-selected or Page filters, Report Filters, anything else already selected!