Forum Discussion

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

DAX formula

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.noitem.novendor
123water
 24milk
 25milk
 26water
223water
 26water
328tea
 29soda
 30sparkling water
 24milk
424milk
 29soda
525milk
 26water
629soda
 25milk
 24milk

1 Reply

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    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