Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
But if it is set to less than 28, the slicer filters my measure, which is not the desired behaviour:
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.
Solved! Go to Solution.
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
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
Perfect, huge thanks @OwenAuger that works a treat, and I really appreciated the explanation and also the simplification. Super user indeed 🙂
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |