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.noitem.novendor123water 24milk 25milk 26 water223 water 26water328 tea 29 soda 30sparkling water 24 milk424 milk 29soda525 milk 26 water629 soda 25 milk 24 milk

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.

DAX formula | Microsoft Fabric Community

4 Replies

v-diye-msft
Community Support
6 years ago
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
  Frequent Visitor
  6 years ago
  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".
  - Anonymous
    Not applicable
    6 years ago
    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