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
vpatel55
Kudo Commander
Kudo Commander

Cannot get ALL to ignore slicer

I need a measure to count the number of rows where the 'value' field is less than or equal to 28. My measure does this, but I cannot get it to ignore a slicer on a page, which filters the 'value'. The desired output should be 203, whatever the 'value' slicer is set to.

 

 

 

Row count (0-28 value) = 
CALCULATE (
    COUNTROWS ( tbljobs ),
    ALL ( tbljobs[Value] ),
    FILTER ( ALL ( tbljobs[Value] ), ISBLANK ( tblJobs[Value] ) = FALSE ),
    FILTER ( ALL ( tbljobs[Value] ), tblJobs[Value] <= 28 ),
    FILTER ( tbljobs, ISBLANK ( tblJobs[Previous jobId] ) = FALSE )
)

 

 

 

If the slicer is set to over 28 (such as 120) the measure successfully ignores the slicer:

 

vpatel55_0-1615397976028.png

 

But if it is set to less than 28, the slicer filters my measure, which is not the desired behaviour:

 

vpatel55_1-1615398031742.png

 

I have uploaded the PBI file here, with dummy data:

https://www.dropbox.com/s/4cvclx4cgf9amvc/Sample%20data.pbix?dl=0

 

A kudos for anyone that can get the measure to ignore the slicer.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @vpatel55 

The reason that the Value slicer is not being ignored is the last argument of calculate:

 

 

FILTER ( tbljobs, ISBLANK ( tblJobs[Previous jobId] ) = FALSE )

 

 

In this expression, tbljobs returns rows of the tbljobs table visible in the filter context, which includes the effect of filters on the Value column. This means the existing filters on tbljobs are retained (filtered to nonblank Previous jobID), and intersected with the other filters specified within CALCULATE.

 

I would suggest this measure. I have simplified the first two filter arguments within CALCULATE as well as changing the last argument to a filter on the Previous jobId column only. Also, ALL ( tbljobs[Value] ) is not needed since you are applying other specific filters to tbljobs[Value] which will automatically replace any existing filters on this column:

 

 

Row count (0-28 value) = 
CALCULATE (
    COUNTROWS ( tbljobs ),
    NOT ISBLANK ( tblJobs[Value] ), -- simplified
    tblJobs[Value] <= 28, -- simplified
    NOT ISBLANK ( tblJobs[Previous jobId] ) -- change to filter on this column only
)

 

 

If you do need any filters to be intersected with current filter context, you can wrap them in KEEPFILTERS.

 

Does this measure give the result you want?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @vpatel55 

The reason that the Value slicer is not being ignored is the last argument of calculate:

 

 

FILTER ( tbljobs, ISBLANK ( tblJobs[Previous jobId] ) = FALSE )

 

 

In this expression, tbljobs returns rows of the tbljobs table visible in the filter context, which includes the effect of filters on the Value column. This means the existing filters on tbljobs are retained (filtered to nonblank Previous jobID), and intersected with the other filters specified within CALCULATE.

 

I would suggest this measure. I have simplified the first two filter arguments within CALCULATE as well as changing the last argument to a filter on the Previous jobId column only. Also, ALL ( tbljobs[Value] ) is not needed since you are applying other specific filters to tbljobs[Value] which will automatically replace any existing filters on this column:

 

 

Row count (0-28 value) = 
CALCULATE (
    COUNTROWS ( tbljobs ),
    NOT ISBLANK ( tblJobs[Value] ), -- simplified
    tblJobs[Value] <= 28, -- simplified
    NOT ISBLANK ( tblJobs[Previous jobId] ) -- change to filter on this column only
)

 

 

If you do need any filters to be intersected with current filter context, you can wrap them in KEEPFILTERS.

 

Does this measure give the result you want?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Perfect, huge thanks @OwenAuger that works a treat, and I really appreciated the explanation and also the simplification. Super user indeed 🙂

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.