DAX to show full order history by selecting a single item in order

[Anonymous]

Hi Community, 

I want to be able to click on a specific material number in a table, and show all sales orders that contain that specific material numbers. I have been able to do so, but I would also like to see all the other material that is included in the sales order. 

Below is the desired outcome. I click on material 1000092985, and two sales ordres show up containing the material number in another table, but also the remaing materials. 

Is this possible? 

Thanks. 

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT(
    'Table'[material number])

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table 2'[material number])
var _salesorder=
SELECTCOLUMNS(
    FILTER(ALL('Table'),'Table'[material number]=_select),"1",[Sales Order])
return
IF(
    MAX('Table'[Sales Order]) in _salesorder ,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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