Forum Discussion

vkoukuntla's avatar
vkoukuntla
New Member
2 years ago
Solved

Required Unique data when 2 tables are merged

Hi,

 

I have below 2 tables - SOH table (no unique rows) & Order (no unique rows)

 

SOH: 

Item#SOHSite Location
A6XY
A3OO
B9XY
B12DD

 

Order:

 

 

Result required as below, if no site selected:

 

 

 

if  site "XY" selected then required below result:

 

 

Thanks in advance.

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi vkoukuntla - Ok , Can you please try the below one

     

    it works

    Total SOH =
    VAR SelectedSite = SELECTEDVALUE('Ordert'[Site Location])
    RETURN
    IF(
        ISBLANK(SelectedSite),
        CALCULATE(
            SUMX(
                VALUES('Ordert'[Item#]),
                CALCULATE(SUM('Ordert'[SOH]))
            )
        ),
        CALCULATE(
            SUM('Ordert'[SOH]),
            'Ordert'[Site Location] = SelectedSite
        )
    )

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi vkoukuntla - Create a measure to calculate the total SOH based on the selected site or overall.

     

    i have used merge transformation to ordertable:

    quantity dont summarize on your table chart. 

    use below measure 

     

    with XY selection:

     

    Total SOH =
    VAR SelectedSite = SELECTEDVALUE('Ordert'[Site Location])
    RETURN
    IF(
        ISBLANK(SelectedSite),
        CALCULATE(
            SUM('Ordert'[SOH]),
            ALLEXCEPT('Ordert', 'Ordert'[Item#])
        ),
        CALCULATE(
            SUM('Ordert'[SOH]),
            'Ordert'[Site Location] = SelectedSite
        )
    )

     

    without XY Selection

     

     

    Hope it works

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

  • vkoukuntla's avatar
    vkoukuntla
    New Member
    2 years ago

    Thank you Rajendra, however when you unselect XY site,  the total SOH for item A should be 9, in your example it shows qty: 18 

    • rajendraongole1's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      2 years ago

      Hi vkoukuntla - Ok , Can you please try the below one

       

      it works

      Total SOH =
      VAR SelectedSite = SELECTEDVALUE('Ordert'[Site Location])
      RETURN
      IF(
          ISBLANK(SelectedSite),
          CALCULATE(
              SUMX(
                  VALUES('Ordert'[Item#]),
                  CALCULATE(SUM('Ordert'[SOH]))
              )
          ),
          CALCULATE(
              SUM('Ordert'[SOH]),
              'Ordert'[Site Location] = SelectedSite
          )
      )

       

       

      Did I answer your question? Mark my post as a solution! This will help others on the forum!
      Appreciate your Kudos!!

       

    • Irwan's avatar
      Irwan
      Icon for Super User rankSuper User
      2 years ago

      hello vkoukuntla 

       

      this is not merging two table, instead using measure.

       

      the merge table and Order table are looking similarly.

       

      1. Create a new measure with following DAX:

      SOH = 
      var _Item = SELECTEDVALUE('Table 2'[Item#])
      Return
      SUMX(FILTER('Table 1','Table 1'[Item#]=_Item),'Table 1'[SOH])
       
      2. Plot 'Order' data in Table visual including measure above with 'Site Location' slicer.
       
      Hope this will help you.
      Thank you.