Forum Discussion

Torudd-55's avatar
Torudd-55
Frequent Visitor
6 years ago
Solved

DAX formula

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 t...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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.

     

Torudd-55's avatar
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's avatar
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