cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pmorg73
Helper V
Helper V

detatched slicer key and filtering issue

Hi everyone.

I am building my first detatched slicer key table.

Pmorg73_0-1618867603570.png

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

Pmorg73_1-1618867671532.png

I then have this measure to group

 

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

if(HASONEVALUE('Size Slicer'[Size]),
SWITCH(TRUE(),
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.
 
Pmorg73_2-1618867771905.png

 

 

Q:

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?

 

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User III
Super User III

Hi,

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Link to a pbix file below

 

pbix file 

 

images below. non sliced page

Pmorg73_0-1618950727074.png

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

 

Pmorg73_1-1618950776880.png

 

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.

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Phil

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
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!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors