Filtering on visuals not quite working

[jessimica1018]

Hey all!

I am hoping someone can help to maybe point us in the right direction. We are having a few issues how a few charts are filtering on our report.

First Chart:

A count chart to show the of Accept vs Declined during a given time period. There are occasions where a candidate will decline and soon after change their mind and accept; as well as accept then change their minds after to reject the contract. The date range filter – if it includes both dates then only the “Final” or last contract status is count; if it doesn’t include both then it counts the one in that range. (This part is still a work in progress – to get the dynamic counts in…. whole other issue….)

Date Filter:	10/1/2022 - 10/7/2022
Department	Accepts	Declines
Twinkle	1	2
Little	1
Star	1
Date Filter:	10/8/2022 - 10/15/2022
Department	Accepts	Declines
Twinkle	1	1
Little	1	1
Star		2
Date Filter:	10/1/2022 - 10/31/2022
Department	Accepts	Declines
Twinkle	2	2
Little	2	1
Star	1	2

Second Chart:

A details chart to show the information behind the counts.

ID

Caregiver.id

Name

Department

Assignment Type

Account Manager2

DateAdded

StartDate

ScheduledEnd

It’s all coming from the same query, shouldn’t be a relationship issue – however there are two merged queries; accepts and declines.  Both are Left Outer join, with the Placement Record being the Source and the declined or accept being the matching.

Now that the picture is painted, when we click on “Twinkle” Accepts #; in theory it should filter the details section to those that make up the count, but instead it is filtering to just anything in the “Twinkle” department regardless of their status.

Under Format > Edit Interactions, both are set to filter; everything on the page set to filter.

I have linked a Google Drive Folder with the three test data sets and the test pbix file

What are we missing?! We have been spiraling on this for about two weeks.

Thank you!!!!!!!!!

[HotChilli]

"Now that the picture is painted, when we click on “Twinkle” Accepts #; in theory it should filter the details section"

--> Where are we clicking on Twinkle Accepts?  A slicer or a section of the first table.  If it's the table, I suspect you are hoping that the particular section of the first table is going to pass "Twinkle" department AND the Count of Accepts to filter the details row but if you look at the funnel icon on the details section, it just passes the department.  That's how visual filtering works. 

-

It looks like you could create a dimension for Accepts (see the last part of my answer) and use that in a slicer and that would filter the details table.  I don't know if that's acceptable to you.

-

I haven't looked in detail at the power query merge and the fact table to see if it all makes sense. I think that's a separate question.

-

Also the picture doesn't quite look like the provided pbix. We are missing all the slicers.  If you want to use all those slicers, it's better to create dimension tables to filter the fact table.  Otherwise it can be a hassle changing the 'edit interactions' between visuals to make it do what you want.

Let me know what you think

[jessimica1018]

We are clicking on the table; yes we were hoping both department and the details of the count of accept to filter. I swear we've gotten that to work before, but different data and different setups. 

A dimension is definitely do-able. I'll go give that a try!

Thank you!