Text filter on unrelated tables

[Anonymous]

Looking to filter an ID between 2 unrelated tables\

i have a text filter that upon entering a value will filter both tables

Table 1 field is called ParentID

Table 2 field is called Case ID

the numbers in tables have the same values but i cannot relate the tables with causing circular relationships

any help would be appreciated

thanks

[selimovd]

Hey @Anonymous ,

in this case you can filter it with CALCULATE. Like this you can use a value from one table and use it as a filter criteria for the other table.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd 

still unsure how to apply it 

i tried this formula with no luck

CALCULATETABLE(FILTER(

ALL('TABLE1'),

'TABLE1'[Id] in VALUES('TABLE2'[SF ID])

))

i get the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

so not sure where to go as the next step here

then ive created a new table with the above formula, do not get any errors but unable to filter

[selimovd]

Hey @Anonymous ,

maybe you can explain what exactly you want to do. CALCULATETABLE creates a table when the data model is loaded. From then on the table will not react to the selection of slicers.

If you want for example to change a measure based on the slicer, you could use something like this:

Sales by ID =
VAR vSlicerID = MAX( SlicerTable[SlicerID] )
RETURN
    CALCULATE(
        SUM( Sales[SalesAmount] ),
        Sales[SlicerID] = vSlicerID
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd 

i am trying to filter on 2 unrelated tables with a single text filter visualisation

when i join the 2 tables i get a circular reference error so i am forgoing that and trying to have a single input that will filter the relevant information in 2 separate table visuals, which in this case is 2 separate data models that have the same ID in both tables

i do not need any aggregation, i just want to filter on the specific information

[selimovd]

Hey @Anonymous ,

when you use a measure you always need an aggregation.

When you don't use a measure (e.g. calculated table or column) it's not dynamic, so it won't react to any slicer. So you need an aggregation for sure.

Can you share the file or explain with an example what exactly you want?

Best regards

Denis

[Anonymous]

is there documentation you can provide?

thanks