Forum Discussion

AishwaryaS's avatar
AishwaryaS
Frequent Visitor
5 years ago
Solved

Slicer Interaction

Hi,

 

I have a 2 tables like below:

 

Table 1:

ItemIDQty
TV 1002  4
TV 1003  12

 

Table 2:

Item  IDStock Qty    Date
TV  1002 12 11.03.2021
TV  1003 5 12.03.2021
TV  1003 3

 12.03.2021

 

In the Power BI Report I show the below table with ID as a Slicer:

 

when no selection is made on the slicer the table should appear as below by taking the sum of Quantity of table1 and max of date of table2 for the particular item.

 

Item Qty    Date   StkQty
TV 16   12.03.2021    20

 

when ID=1002 is chose in the slicer the table should appear as below:

 

Item  Qty  Date  StkQty
TV  4  11.03.2021  12

 

Thanks in advance!

Need Help
  • sayaliredij's avatar
    sayaliredij
    5 years ago

    1. Make connection between Table 1 and Table 2 using ID

     

    Create measures as following

     

    QTY = SUM(table1[Qty])

    StkQty = SUM(table2[StockQty])

    Mxdate = MAX(table2[date])

     

    when you create table visualization you will get results as follows

     

    Regards,

    Sayali 

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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, AishwaryaS 

     

    Try to create 3 measures below:

    _Sum of Qty = SUM('Table 1'[Qty])
    _Sum of Stock Qty = SUM('Table 2'[Stock Qty])
    _MaxDate = MAX('Table 2'[Date])

    Result:

     

    Is this the result you want? Hope this is useful to you

    Please feel free to let me know If you have further questions

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    1. Make connection between Table 1 and Table 2 using ID

     

    Create measures as following

     

    QTY = SUM(table1[Qty])

    StkQty = SUM(table2[StockQty])

    Mxdate = MAX(table2[date])

     

    when you create table visualization you will get results as follows

     

    Regards,

    Sayali 

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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, AishwaryaS 

     

    Try to create 3 measures below:

    _Sum of Qty = SUM('Table 1'[Qty])
    _Sum of Stock Qty = SUM('Table 2'[Stock Qty])
    _MaxDate = MAX('Table 2'[Date])

    Result:

     

    Is this the result you want? Hope this is useful to you

    Please feel free to let me know If you have further questions

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.