resell report

[Anonymous]

I am trying to build a report showing all items sold on orders with selected products.  I have two tables.  One has orders and th4e other has order details.  I want to be able to select a single product from the details talbe then show all orders that had this item on them.  I can show totals from the orders table but I also want to show the details ffrom the detaikls table but I don't want to limit the details to the select items.  this is what I am talkign about.

Orders   
OrderID   Sales   Tax

1              5.0       0.5

2              25.0     2.5

3              10.0     1.0

OrderDetails
OrderID    ItemID   Sales
1               a            2

1               b            3

2               a            2

2               c            23

3               b            3
3               c             7

If these are the two tables and I selet Item b

I want to return this data
OrderID   Item   Sales

1              a         2

1              b         3

3              b         3

3              c          7

The b4est way to describe this is a cosell report telling me what items are sold with a selected item.  I can get the summary data from the roders table but I am unsure how to go back and get the details from those orders without havign it filtered by the selection.

[Vvelarde]

@Anonymous

Hi, try with this

1. Create a new table

ItemsID=Values(OrdersDetail[ItemsID])

2. Create a measure:

FilterM =
VAR _OrderID =
    SELECTEDVALUE ( OrderDetails[OrderID] )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( OrderDetails ),
                OrderDetails[ItemID] IN VALUES ( ItemID[ItemID] )
                    && OrderDetails[OrderID] = _OrderID
            )
        )
            > 0,
        1,
        BLANK ()
    )

3. Put the measure in the Visual level filter and set to 1

Regards

Victor

[AlB · Accepted solution]

Hi @Anonymous

Try the following:

1. Create a new one-column table, FilterTable, with all values of OrderDetails[ItemID] in FilterTable[ItemID]. Set FilterTable[ItemID] in a slicer. This will be used to select the the item of interest.

2. Place a table visual in your report and set in values OrderDetails[ItemID], OrderDetails[OrderID] and OrderDetails[Sales]. Choose "don't summarize" for all three. 

3. Create this measure that will determine what items are to be shown:

ShowMeasure = 
VAR _OrdersList =
    CALCULATETABLE (
        DISTINCT ( OrderDetails[OrderID] );
        OrderDetails[ItemID] IN DISTINCT ( FilterTable[ItemID] );
        ALL ( OrderDetails )
    )
VAR _CurrentOrder =
    SELECTEDVALUE ( OrderDetails[OrderID] )
RETURN
    IF ( _CurrentOrder IN _OrdersList; 1 )

4. Place [ShowMesure] in the visual level filters of the table visual and select "Show values when the value is:" --> 1

Code formatted with  

[Anonymous]

Thanks this works