Combine PowerBI DAX Filter and SELECTCOLUMN

Question

I want to create a new table based on this one:

that filters for Warehouse=2 and "drops" the columns "Price" and "Cost" like this:

I have managed to apply the filter in the first step using:

FILTER(oldtable;oldtable[Warehouse]=2)

and then in the next step cold create another table that only selects the required columns using:

newtable2=SELECTCOLUMNS("newtable1";"Articlename";...)

But I want to be able to combine these two functions and create the table straight away.

Pituitary

Hi... Some of us don't click mystery URLs. You can add pictures directly to your question. – Surbased

Hi, Unfortunately I am new here and you can only post images with reputation>10. – Pituitary

@MikeSmith-MCT-MVP - I changed it now :) – Pituitary

Answer 1

This is very simple, because in your first step, a table is returned which you can use directly in your second statement.

newTabel = SELECTCOLUMNS(FILTER(warehouse;warehouse[Warehouse]=2);"ArticleName";warehouse[Articlename];"AmountSold";warehouse[AmountSold];"WareHouse";warehouse[Warehouse])

If you want to keep the overview, you can also use variables and return:

    newTabel = 
        var filteredTable = FILTER(warehouse;warehouse[Warehouse]=2)
        return SELECTCOLUMNS(filteredTable;"ArticleName";warehouse[Articlename];"AmountSold";warehouse[AmountSold];"WareHouse";warehouse[Warehouse])

Gautious

Hi again, Aldert! Both solutions work great-thank you for that. However, when I apply your solutions, the columns get put in a different order like this Warehouse->ArticleName->Amount sold. But I actually want the order like in the statement so ArticleName->AmoundSold->Warehouse. Do you know how to fix this? – Pituitary

@LorenzJoe, the order in your tables in not relevant in PowerBI. The reason is that you control the order in your visuals. I also wonder a bit why you need this table for wharehouse 2 only because you can use the visual to filter only on warehouse 2. – Gautious

Oh okay, makes sense. I actually need to filter the amountSold as there are some errors in the data that have to be cleaned before further data modeling (the answer you gave to my previous question of the inventory). But the same logic applies. Thanks again – Pituitary

@LorenzJoe, if you have some errors in your data, you should take care of this in mquery. This ensures that for all visuals, you are working with the clean data. – Gautious

Answer 2

Instead of selectcolumns you can use addcolumns, it is one step in all

Panelist

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Colo