DAX formula

[Torudd-55]

Hi!

I would like to get help with a, hopefully, easy question. How can i write a DAX command/formula to only select all of the orders in a table that contains an item with a specific vendor. For example, if i only want the orders in the table below containing the vendor "milk", how do i write the DAX formula so it doesn't just show the orders and the specific item with the vendor "milk"? I want the result to show all the orders containing an article with the vendor but still vizualises all of the items whitin the order. In the table below, the order "2" should be removed but the rest vizualised as it is.

Thanks in advance!

order.no	item.no	vendor
1	23	water
	24	milk
	25	milk
	26	water
2	23	water
	26	water
3	28	tea
	29	soda
	30	sparkling water
	24	milk
4	24	milk
	29	soda
5	25	milk
	26	water
6	29	soda
	25	milk
	24	milk

[lc_finance]

Hi @Torudd-55 ,

Here is how to prepare a calculated column to use for this filter.

You can download the Power BI file for my proposed solution from here.

Here are the steps:

1) I completely filled the order no so that each row has an order number

2) I added a calculated column for the orders with milk (Modeling -> New column)

Below is the DAX formula:

Orders with milk = 
CALCULATE(CONTAINS('Orders',[vendor],"milk"),
ALLEXCEPT('Orders','Orders'[order.no]))

Here is a screenshot of the new column, which you can use to filter out the table:

I hope this helps you. Do not hesitate if you have more questions.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com