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
Anonymous
Not applicable

How to calculate filtered and unfiltered total rows while using a slicer

ilky_0-1617841818600.png

Hi, 

I have a scenario where I need to calculate the number of rows in a table.  I also have a slicer for the "Flag" field. 

My problem is, if I select Yes from the slicer, because the table gets filtered I get the the same number of rows for :

Total number of rows for A  = 2

Total number of rows for Flagged A = 2

I would like to create a measure that regardless of the filter from the slicer, I get :

Total number of rows for A = 5

How can I do that ? I have tried some measure and still could not work it out. Thanks for your help.

Ilky

5 REPLIES 5
Anonymous
Not applicable

Thanks all, my issue was more complicated than what I explained, with @Ashish_Mathur help I have come up with a measure to fix the problem. Unfortunatelly I cannot share the details of the data and making a moke up version wood take a bit of time. Thanks again for your inspration and help 🙂

Regards

Ilky

v-angzheng-msft
Community Support
Community Support

Hi,  @Anonymous 

You can actually create just one measure to get the results you want

Try to create a measure below:

 

Only one measure = COUNTROWS(FILTER('Table','Table'[Name]="A"))

 

Sample data:

222.png

You may wonder how a measure can make it,

You can create two visual objects, and disable the interaction between one of the visual objects and the slicer.

 

The disadvantage is that you need to create two visual objects.

If you find this inconvenient, you can also create two measures

 

Total number of rows for A = COUNTROWS(FILTER(ALL('Table'),'Table'[Name]="A"))
Total number of rows for Flagged A = COUNTROWS(FILTER('Table','Table'[Name]="A"))

 

Result:

4444.png

As a reminder, if you want to display more data in the table visual,

you can add an index column in Query Editor.

 

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

Try this measure

Measure1 = =calculate(countrows(data),all(data[flag]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Wouldn't this count all rows based on the Flag, I want to count based on the Name field. 

Imagine slicer is selected to No  : 

The total row count for A is still 5 but the number of A with Nos would be 3.

 

Please try and let me know.  If my formula does not work, then share the link from where i can download your PBI file.  Show the exact problem there and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.