Showing results for 
Search instead for 
Did you mean: 
Helper V
Helper V

detatched slicer key and filtering issue

Hi everyone.

I am building my first detatched slicer key table.


simple data model view above. its currently a small data table but I will expand it once working properly.


The table I have built takes the floor area of a house and splits in to the following


I then have this measure to group


Size =
var _area = 'house drill'[Area]

if(HASONEVALUE('Size Slicer'[Size]),
VALUES( 'Size Slicer'[Key] )=1 && _area<210, _area ,
VALUES( 'Size Slicer'[Key] )=2 && _area>210 &&_area<=350, _area ,
VALUES( 'Size Slicer'[Key] )=3 && _area>350 && _area<=600, _area ,
VALUES( 'Size Slicer'[Key] )=4 && _area>600, _area) ,
IF(BLANK(), BLANK(), _area ) // this is if nothing is clicked but excludes blank
I got the idea from ruth's last video at Curbal, so kudos to Ruth.
So when applied to a page it filters my main table of values but not the cards on the right of the image below.




I am trying to use the slicer to then filter a calculation of the average billable amount column divided by the area.

I used a calculated column to divide these two data columns. Then have a measure to sum the resulting calc column.


When I click the "Size" detached key it filters the main table but it is ignored in the sum and average measures.

Any suggestions?






You may download my PBI file from here.

Hope this helps.


Ashish Mathur

View solution in original post

Super User III
Super User III


Share a small dataset that can be pasted in MS Excel (or a link from where your PBI file can be downloaded) and show the exact expected result.

Ashish Mathur

Link to a pbix file below


pbix file 


images below. non sliced page


sliced page. The Size slicer is the detached table. It slices the main table but not my cards




I am trying to work out the average. the data is individual time entries on each job. I then sum those up. Divide that by the floor area for the measure "Sum Billable per m". I have used calculated columns in this process.


I then count the job number rows and do a divide to work out the average.


Note there is a manual table called "Complexity". I am trying to develop it so that we do not use this any more. It is a manual list of job numbers, so would not be dynamic. included to show everything though.


So my query is this. What do I need to change so that the "size" slicer also correctly changes the count? This will then alter the average calc by default. If you use the size slicer you will see it only changes the main table.



You may download my PBI file from here.

Hope this helps.


Ashish Mathur

View solution in original post

Many thanks, will drill in a work out what you did. The next thing I was going to try was calculations with filters, which I think would be on the road to what you have done. I have only just scratched the measures you created though.


Many many thanks



You are welcome.

Ashish Mathur

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors