Apply a Filter Cross-Datasets

Hi

Please could someone clarify what “Filtering is performed only when all target fields are mapped” means? Does that mean that to filter across datasets, the Source dataset’s columns must be a subset of the target dataset’s columns, with no additional columns? What is the use of that?

Thx

Hi @Jeremy_Likezero,
When you’re setting up a filter across datasets, the fields (and the values) have to match exactly. This means the title, data type and values (case sensitive) have to completely match for this to work.

For additional information, I’ve included an article below for you to explore as well.

Let us know if you have any additional questions!

Thank you for that @Brett. I have read that article but am none-the-wiser. I am trying to create relational links between datasets, like I have been able to do in Excel/Powerquery forever… I want to be able to have a filter on “Value” and for the “Agreement Id” to filter the other datasets in the visual. If this is not the way to do this in QS, I would be grateful to know how to achieve this basic task.

Hi @Jeremy_Likezero,
If you’d like to use a field from one dataset to affect/filter fields in a different dataset, you’ll need that field (Agreement ID for your case) to also be in your secondary dataset. Then you could explore using a parameter to filter your visuals.

If the second dataset does not have the same field (includes all value options matching along with being case sensitive), you’ll need to perform a table join for this functionality.

QS only allows cross dataset filtering when the field handling the filtering is available in both fields.

Let me know if you have any additional questions

That’s the bit I don’t get. “Agreement Id” is present in every dataset. Why does every field in the source dataset have to be mapped to the target?

How can I populate a parameter from one filter and then use that to control another filter on the same page?

I think I can’t do what I want to do, but I would like to understand what the cross-dataset filtering offers if you have to be able to map every field from the source to the target…

Hi @Jeremy_Likezero,
From my experience, you don’t need every field to be matching for the field mapping to take place so this is a bit odd.
If you could share some additional info, let’s see if we can’t find a work around for your case!

So in regards to the filter for ‘value’. How will you be filtering that and what will it be affecting?
Will it be used to limit the results returned in Agreement ID?

Any screenshots of your visuals that you could include could be helpful as well to show what you’d like the finished product to look like

So, here are a fact and denormalised dimension tables:
Dimension:
SELECT *
FROM (VALUES
(1, ‘Complete’, ‘Yes’),
(2, ‘Complete’, ‘No’),
(1, ‘Tranche’, ‘1’),
(2, ‘Tranche’, ‘2’)
) tbl(
“Agreement Id”,
“Metadata Field”,
“Metadata Value”
)

Fact:
SELECT *
FROM (VALUES
(1, ‘AG1’, ‘2022-01-01’::date),
(2, ‘AG2’, ‘2023-01-01’::date),
(3, ‘AG3’, ‘2024-01-01’::date),
(4, ‘AG4’, ‘2025-01-01’::date)
) tbl(
“Agreement Id”,
“Agreement Ref”,
“Agreement Date”
)

I want to select the Completed agreement.

But the filters show:

(I could give you the bundle, but this page will only upload images.)

Thanks @Brett

Hi @Jeremy_Likezero

I am not sure why you have these two as separate datasets instead of joining the dimension and fact table in a single dataset. That would have been straight forward.

Anyways, coming to your use case for the two visuals to interact with each other there should be a common element that should be passed from the source visual to the target.

I presume you have the Filter Scope in your Filter action as All fields which tells QuickSight that all the fields in the source should be used to filter the target visual

Since the target visual has only agreement Id as the common field and the other two can not be linked or logically joined to a target column it complains. If you change the scope Selected fields and only map the agreementId things would work fine.

The way you have set the Parameter Controls are only filtering the visual on the dimension but that will not trigger filtering on the Fact visual since the Filter action only triggers when you interact with your Source visual which in your case is the visual on the Dimension table.

So you can actually remove the Parameters on the Metadata Field and Metadata Value.
Include the Agreement ID in the Source Visual, you can hide this column as you want
Add the Filter action with scope of Selected field and check the Checkbox only on Agreement ID field (it is available for mapping since you have it in the visual though hidden)

Then when you click on a row in the source visual the target would filter accordingly

i

Hope this helps.

Thanks @Giridhar.Prabhu

The reason I don’t denormalize is because the real data is millions of rows and multiple dimensions. I have tried creating massive combined datasets (I have used them before in Qlik) but found the performance of SPICE was not up to the job. (Maybe AWS are a bit stingy with the CPU!)

I hadn’t considered that the filtering could be a two-stage operation for the user, ie filter the dimension and then click a row in the filtered visual to trigger an action. I will probably have to go with that.

However, the question still remains, what is the purpose of the cross-dataset filtering? I cannot see how it can ever be useful if every field in one dataset has to exist in the other!

Hi @Jeremy_Likezero

Not all fields. You need to have the fields that you want to drive the filtering

Regards,
Giri

I believe you are referring to the fields in the filter action. My question is about the cross-dataset filtering (as shown in my screenshots).

Hi @Jeremy_Likezero,
It’s been awhile since last communication on this thread. Were you able to find a work around for your case?

Jumping back in on the cross-dataset filtering as I tested out using cross-dataset filtering recently. In my case, every field did not need to match but the values had to be the same in the fields that I was matching between datasets.

Let us know if you have any additional questions, if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @Jeremy_Likezero,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!