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

Overall data measure using allexcept

Hi Guys! 

I am trying to create an overall measure which looks at the data which we are currently looking at on the dashboard vs. the entire set of data that we have. 

It will be used to extrapolate the accuracy which we show on the dashboard, to the entire set.

 

The only filter which it should include is the datefilter as I would like to see what are the total rows within this time period vs. what aree the rows we are looking at on the dashboard currently.

 

My formula looks something like this:

 

CCD view measure =
var ccd_count = CALCULATE(COUNTROWS('Customs Clearance Document'),ALLEXCEPT('Customs Clearance Document','Customs Clearance Document'[Date Filter]))
var ccd_in_view = [Total CCD Count]

RETURN ccd_in_view/ccd_count
 
But it always considers the other filters on the dashboard as well. 
 
Has anyone built this filter before? 🙂 
 
Thank you so much for the help! 
1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@ryan_ward 
We can use Allexcept in calcuate columns, but to create a measure try use the following filter.

 

CCD view measure =
var ccd_count = CALCULATE(COUNTROWS('Customs Clearance Document'),Filter(Allselected('Customs Clearance Document','[Date Filter]=MAX([Date Filter]))
var ccd_in_view = [Total CCD Count]
 
RETURN ccd_in_view/ccd_count
 
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@ryan_ward 
We can use Allexcept in calcuate columns, but to create a measure try use the following filter.

 

CCD view measure =
var ccd_count = CALCULATE(COUNTROWS('Customs Clearance Document'),Filter(Allselected('Customs Clearance Document','[Date Filter]=MAX([Date Filter]))
var ccd_in_view = [Total CCD Count]
 
RETURN ccd_in_view/ccd_count
 
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Amazing! Thank you so much to everyonewho has assisted with this! 🙂

amitchandak
Super User
Super User

@ryan_ward , here ccd_count should only consider filter and context of Customs Clearance Document'[Date Filter]

 

if you do not want any filter then use all

Hi @amitchandak Thank you! The only filter that the ccd_count should consider is the date filter, all other filters should not affect the denominator value at all. In this way we can filter the denominator value by the date, while the numerator value will be affected by all filters on the dashboard. Sorry if I did not understand your response.

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.