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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jopezzo
Helper I
Helper I

How to filter a query based on the value of another query (with the possibility to show all items)

Hi All!

I am trying to create a model as follows, in Excel:

I have a table called tData, with 2 columns: Supplier and Value:

 

tSupplier.jpg

 

I have a list of all the suppliers + one row which is "ALL". 

 

Suppliers.jpg


I am using this list to create a drop-down menu to select a supplier or the string "ALL".

 

Dropdown.jpg

This table is called tSupplier.

The table tData is loaded. The table tSupplier is loaded as well. I do a drill-down on the Supplier to only have the value from the drop-down menu in the table tSupplier.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="tSupplier"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}}),
    Supplier = #"Changed Type"{0}[Supplier]
in
    Supplier

 

 

This gives me a query supplier, where there is only one value (the one selected with the drop-down menu in Excel).

 

Here is my query:

 

Query.jpg

 

It is the last step that I would like to change. I would like that if the selected value in the dropdown menu is "ALL", I get all the suppliers (basically, no filter applied), otherwise, I I would like the filter as in the above screenshot.

 

I have uloaded a data sample here.

 

Any idea how to solve that?

 

1 ACCEPTED SOLUTION

Hi Mariusz,

 

The code you proposed did not work exactly as I wanted. With the "true" argument in the end, I always had all items (nothing filtered). With the "false" argument, I had the selected value filtered, but when I selected "ALL", the table was empty.

 

However, you gave me the idea how to solve it.

 

I created a conditional column to test whether the supplier from the drop down corresponded to the supplier in the query. If not, "ALL". Then I just had to create a filter with [Supplier] = Supplier. 

 

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Value", Int64.Type}}),
    #"Added SupplierFilter" = Table.AddColumn(#"Changed Type", "SupplierFilter", each if [Supplier] = Supplier then [Supplier] else "ALL",type text),
    #"Filtered [SupplierFilter] = Supplier" = Table.SelectRows(#"Added SupplierFilter", each ([SupplierFilter] = Supplier)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered [SupplierFilter] = Supplier",{"Supplier", "Value"})
in
    #"Removed Other Columns"

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @jopezzo 

The pbix link is not valid.

If Mariusz's answer sloves your problem, could you kindly accept it as a solution?

If not, feel free to let me know.

 

Best Regards
Maggie
Mariusz
Community Champion
Community Champion

Hi @jopezzo 

 

Try adjusting the step to something like.

= Table.SelectRows(#"Changed Type", each ( if [Supplier] = Supplier then [Supplier] = Supplier else true ) )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Hi Mariusz,

 

The code you proposed did not work exactly as I wanted. With the "true" argument in the end, I always had all items (nothing filtered). With the "false" argument, I had the selected value filtered, but when I selected "ALL", the table was empty.

 

However, you gave me the idea how to solve it.

 

I created a conditional column to test whether the supplier from the drop down corresponded to the supplier in the query. If not, "ALL". Then I just had to create a filter with [Supplier] = Supplier. 

 

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Supplier", type text}, {"Value", Int64.Type}}),
    #"Added SupplierFilter" = Table.AddColumn(#"Changed Type", "SupplierFilter", each if [Supplier] = Supplier then [Supplier] else "ALL",type text),
    #"Filtered [SupplierFilter] = Supplier" = Table.SelectRows(#"Added SupplierFilter", each ([SupplierFilter] = Supplier)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered [SupplierFilter] = Supplier",{"Supplier", "Value"})
in
    #"Removed Other Columns"

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors