Reply
avatar user
Anonymous
Not applicable

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

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. 

 

Eivindeiki_2-1678176081190.png

Is this possible? 

Thanks. 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Here are the steps you can follow:

1. Create calculated table.

 

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

 

vyangliumsft_0-1678341316327.png

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.

vyangliumsft_1-1678341330135.png

4. Result:

vyangliumsft_2-1678341335375.png

 

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

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Here are the steps you can follow:

1. Create calculated table.

 

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

 

vyangliumsft_0-1678341316327.png

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.

vyangliumsft_1-1678341330135.png

4. Result:

vyangliumsft_2-1678341335375.png

 

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)