DAX code filtering tabled based on selected value (show other values)

[Anne_PBI]

Hi all,

I have an issues to which I cannot find the solution. I hope someone can help me out. I've created an example dataset to explain the problem: customers order food and drinks in a restaurant. They can order multiple types of food and drinks during 1 visit. Here is a sample of 5 different customers with their orders:

I can edit this table to make it a one record per customer list:

Now what I would like to know is when I select one item in a slicer/filter, what are the other most ordered items per type (food/drinks) based the customers that ordered the selected item?

Example:

I select as item Fries. Customer B, C and E ordered Fries. During the same order, they also ordered other food and drink items. I want to count the number of times the other items were ordered to see which item is most often ordered together with fries. This is what I would like to see then:

My thoughts were to first create new DIM table with the type and item combinations (a food and drinks list) which can then be used for the filter. I can create a relationship between the new DIM table and the FACT table in the model view so that I can filter the customers with orders that contain the selected item. Then I somehow need to use this list of selected customers to filter another table (a copy of the original FACT table with all the customers and their order information?) so that I can see the whole order of these customers. And then I can create a visual of the number of items, per type, in which I exclude the selected item.

I hope someone can help me out 🙂

Many thanks,

Anne

[v-jtian-msft]

Hi,@Anne_PBI . I am glad to help you.

Your idea is great and I think it can achieve the result you need: see which foods users prefer to order when ordering fries!

Below is a test I did for your idea, I hope it helps.

I created a DIM table with all food and drink types and item combinations

DIM_Items = DISTINCT(SELECTCOLUMNS('Table', "Item", 'Table'[Item], "Type", 'Table'[Type]))

The measure:

Orders_Excluding_Selected = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Item] <> SELECTEDVALUE(DIM_Items[Item]) &&
        'Table'[Customer] IN 
            CALCULATETABLE(
                VALUES('Table'[Customer]),
                'Table'[Item] = SELECTEDVALUE(DIM_Items[Item])
        )
    )
)

Here is my test data:

It should be noted that you need to write a measure suitable for your data according to your actual computing environment, because the value of the measure will be affected by the current computing environment to produce different results, I wish you realize your needs soon.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anne_PBI]

Hi @v-jtian-msft ,

many thanks for your help! It works for the food items but unfortunately not yet for the drink items. I cannot figure out why it doesn't work as you only filter based on the item and not the type. Do you have any ideas? 

Based on the selection of fries, this would be result I would expect:

 

or condensed: 

Kind regards,

Anne

[v-jtian-msft · Accepted solution]

Hi,@Anne_PBI .Thank you for your reply.
Here is my latest code.

DIM_Items_02 = DISTINCT(SELECTCOLUMNS('Table', "Item02", 'Table'[Item], "Customer",'Table'[Customer]))



test_result = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[Item] <> SELECTEDVALUE(DIM_Items_02[Item02]) &&
        'Table'[Customer] IN 
            CALCULATETABLE(
                VALUES('DIM_Items_02'[Customer]),
                'Table'[Item] = SELECTEDVALUE(DIM_Items_02[Item02])
        )
    )
)

The previous version I gave you would have filtered out the same type of food by default, now the newest code meets your existing needs and hopefully helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anne_PBI]

Works perfect! Many thanks Carson Jian 🙂

[v-jtian-msft]

Hi,@Anne_PBI .
You are welcome.