DAX formula

[Torudd-55]

Hi!

Can someone help me with an, hopefully, easy problem. I would like to write a DAX formula that selects all of the orders that contains the vendor "milk" whitout only showing the orderno and the specifik items related to that vendor.

So the result, when looking at the table below, should only remove the order "2" from the visualization.

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

[v-diye-msft]

Hi @Torudd-55 

Let me know if you'd like to get below results:

1. I used your data and fill down the first column:

2.  Use the filter panel:

[Torudd-55]

Hi @v-diye-msft 

I have used the filter panel but just as in your case the other items won't show, only the ones with the vendor "milk". 

I want all of the items whitin the order to show if the order contains one item with the vendor "milk".

[v-diye-msft · Accepted solution]

Hi @Torudd-55 

I just got you, could you please check if you'd like to get this one?

HasMilk = CALCULATE(MAX(Table3[order.no]),FILTER(ALL(Table3),[vendor]="milk"),VALUES(Table3[order.no]))

Then you can filter the measure without blank to get the final results.

[Anonymous]

What worked for me was to create a calculated column in the table (I called my "Orders") - you'll need to do a fill-down on the order.no so you have the order number in each row - that looks at the current order number for each row and looks to see if there's a record that has a vendor of "milk" (I had to do some trimming to get rid of leading/trailing spaces).  Then you just need to filter on HasMilk <> False.

HasMilk =

VAR _currentOrder = Orders[order.no]

VAR _currentRows = FILTER(Orders, Orders[order.no] = _currentOrder && TRIM(Orders[vendor]) = "milk")

VAR _milkCount = COUNTROWS(_currentRows) <> 0

RETURN _milkCount

Hope that helps.  If you find that this answers your question please consider making it as "solved".

Eric