Filter several tables/views in one place?

[soderqvistn]

Hey,

So I have this set of data. On each fact I have to go in a set a filter on MeasureID. I was wonder if anyone has a good idea on how to maek it more efficient. Optimal would be if it was possible to set the filtering in one place at that gets set for each view. Because I need to to this report multiple times and each time limit my data per MeasureID. Help much appreciated!

[v-lid-msft]

Hi @soderqvistn ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @soderqvistn ,

As an addition, we can create such a calculated table use DAX formula like following, then create relation like other people said.

IDTable =
DISTINCT (
    UNION (
        'Table A'[MeasureID],
        'Table B'[MeasureID],
        'Table C'[MeasureID],
        'Table D'[MeasureID]
    )
)

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RossP96]

You could create a feeder table, this would include all measure ID's. If you don't have many ID's then you can just type them out in a new table, or try to duplicate a list with all of them on and remove duplicates. You could also append multiple tables if 1 list doesn't include them all.

Then you would create relationships from all of your measure ID's in to this new table. This new table would then be used to filter everything.

[soderqvistn]

I was thinking about creating a table by using enterdata and set a column called MeasureID and ad the id I want to fitler on. But when I then join that newly created tale to the others, it doersnt filter them. See pic below

[v-lid-msft]

Hi @soderqvistn ,

Have you tried to put the Measure ID Column of the new table into a All-Page Filter, then Select All then unselect the blank?

Please refer to the last reply in this thread: https://community.powerbi.com/t5/Desktop/Massive-Filtering/td-p/828524

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[soderqvistn]

No I don't want to do that solution because I want to minimize the data getting loaded. So I don't want to load everything and then filter it in the report. I would like to make the fitlering earlier in the process if you understand what I mean.. But what doesnt this join filter the other tables?

[v-lid-msft · Accepted solution]

Hi @soderqvistn ,

For example, we have two table all have multi IDs,

If we want to load ID in the FilterTable

We can use the M query like following , if you are not very Familiar with Advanced Editor , you can select one value, then edit the query it generate.

= Table.SelectRows(#"Changed Type", each List.Contains(Table.ToList(FilterTable),[ID]))

All the M query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALGcgyxjMcgGyTMAsVyDLFMxyA7Is4CwzMMsdyDJXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(Table.ToList(FilterTable),[ID]))
in
    #"Filtered Rows"

Then we can get the result

You can test with edit the FilterTable to see how it load the nessary data by following icon.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @soderqvistn ,

Actually when in this Level, all the data have been loaded, The purpose we create the new table and add relation is to filter the data we need in Visual/Report Level. If you just want to load the data you need, we will try to do it in Power Query Editor, but it cannot be dynamic by slicer/filter because it is earlier than visual/report level.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[soderqvistn]

Aha can you think of nay smart way of doing it in Power Query Editor?

Im doing it like this now for every view