Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BenGi
Frequent Visitor

Filter a table using multiple values from an other table

Hello 

I have a table containing a lot of data. These data are catagorized by a RecordtypeID.

I want to filter this table on 2 RecordTypeID.

I would like to use the Recordtype table to filter the 2 RecordType like this

-------

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48, CreateNavigationProperties=true]),
RecordType = Source{[Name="RecordType"]}[Data],
#"Autres colonnes supprimées" = Table.SelectColumns(RecordType,{"Id", "DeveloperName"}),
#"Lignes filtrées1" = Table.SelectRows(#"Autres colonnes supprimées", each ([DeveloperName] = "A" or [DeveloperName] = "B")),
Id = #"Lignes filtrées1"[Id]
in
Id 

-----

and then use this result to filter my fact table at the first step to avoid to load all the data from the source.

--------

let
Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion=48, CreateNavigationProperties=true]),
Case = Source{[Name="Case"]}[Data],
#"Lignes filtrées" = Table.SelectRows(Case, each([RecordTypeId]=??????????))
in
#"Lignes filtrées"

----------

I try using query parameters without success.

Any suggestion to do it ?

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

Good day BenGi,

The List.Contains function may be used. Its first parameter is a list to search. Its second parameter is the value to search for.

If your first table is called “Table 1” then you can replace,

each( [RecordTypeId]=?????????? )

by

each List.Contains( #"Table1"[RecordTypeId], [RecordTypeId] )

#"Table 1"[RecordTypeId] is a list. It is the list of values in the "RecordTypeId" column of "Table 1" .

View solution in original post

1 REPLY 1
collinsg
Super User
Super User

Good day BenGi,

The List.Contains function may be used. Its first parameter is a list to search. Its second parameter is the value to search for.

If your first table is called “Table 1” then you can replace,

each( [RecordTypeId]=?????????? )

by

each List.Contains( #"Table1"[RecordTypeId], [RecordTypeId] )

#"Table 1"[RecordTypeId] is a list. It is the list of values in the "RecordTypeId" column of "Table 1" .

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors