Forum Discussion

Anne_PBI's avatar
Anne_PBI
Frequent Visitor
2 years ago
Solved

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

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 restaura...
DAX
Filter
PBI
relationship
Select
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Anne_PBI
    Frequent Visitor
    2 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anne_PBI
        Frequent Visitor
        2 years ago

        Works perfect! Many thanks Carson Jian 🙂