Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

resell report

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 prod...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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  

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
7 years ago

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