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
NicoleTy
New Member

How to display totals of values filtered out?

Hi,

 

 

I have a table imported into the data model. It has mulitple columns C1, C2, C3. C1 is a set of numbers, C2 and C3 are categories of C1 like State and Zipcode. We need to filter C1 for values under a threshold so the distribution is more clear. We also need to know how many values we filtered out when we apply the threshold.

 

I duplicated C1 to another query and only get the static total count of the values > threshold, when I change C2 and C3, the value does not change. Is there a way to make it display in the same report view?

 

Thanks,

-Nicole

2 REPLIES 2
SqlJason
Memorable Member
Memorable Member

Can you paste some sample data on what the source looks like, and what you need the results look like? Would help us to understand your scenario as well as the logic better.

 

You should be using a measure to do the filtering as well as get the counts, and not a calculated column. If you do it with a calculated column, the values would be static and would not change while filtering C2 and C3. Hopefully, I (or someone else in the forum) will be able to give you the exact formula if you paste the source data.

As mentioned look to do this in a measure and if you are then filtering data but still need to get a value from a filtered out value look to use something like ALL, ALLEXCEPT or VALUES in the measure.

 

http://sqlblog.com/blogs/marco_russo/archive/2010/04/05/all-allexcept-and-values-in-dax.aspx

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.