Forum Discussion

limonSerga's avatar
limonSerga
Regular Visitor
9 months ago
Solved

Show values on table based on columns from another table

Hello 

 

Im having some issues generating a table, hopefully I can get some guidance on how to solve this issue. I would be greatly appreciate it. 

 

Im trying to get the values from "Table B" to be shown on "Table A". My two tables have a relationship based on Date. However when I try to add to "Table A" the values of column "Parts Planned" I get the sum of all the rows on that colum since I can not find a way to relate those values based on part number. 

 

Table A
Datepart numberParts Produced
1/1/2025A50
1/1/2025B100
4/2/2025B50
4/2/2025C25
8/24/2025C5
12/8/2025D100
12/8/2025A150

 

Table B
Datepart numberParts Planned
1/1/2025A100
1/1/2025B150
4/2/2025B200
4/2/2025C250
8/24/2025C50
12/8/2025D150
12/8/2025A200

 

This will be my desired result: 

Table A
Datepart numberParts ProducedParts Planned
1/1/2025A50100
1/1/2025B100150
4/2/2025B50200
4/2/2025C25250
8/24/2025C550
12/8/2025D100150
12/8/2025A150200

 

However, this is what I get when I try to do sumx calculations

 

Table A
Datepart numberParts ProducedParts Planned
1/1/2025A501100
1/1/2025B1001100
4/2/2025B501100
4/2/2025C251100
8/24/2025C51100
12/8/2025D1001100
12/8/2025A1501100

 

Any ideas? 

Thanks in advance for the support! 

  • Smitho's avatar
    Smitho
    9 months ago

    This is an efficient solution and works when you can only choose 1 date.  If there is a need to select multiple dates then try:

    Parts Planned =
    CALCULATE(
    SUM('TableB'[Parts Planned]),
    TREATAS(
    VALUES('TableA'[Date]),
    'TableB'[Date]
    ),
    TREATAS(
    VALUES('TableA'[part number]),
    'TableB'[part number]
    )
    )

5 Replies

  • Zanqueta's avatar
    Zanqueta
    Super User
    9 months ago

    Hi limonSerga,

    I tested here and worked. Please try from your side and let me know if it worked.

    Parts Planned = 
LOOKUPVALUE(
    TableB[Parts Planned],
    TableB[Date], SELECTEDVALUE(TableA[Date]),
    TableB[part number], SELECTEDVALUE(TableA[part number])
)

     

     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.



    • Smitho's avatar
      Smitho
      Regular Visitor
      9 months ago

      This is an efficient solution and works when you can only choose 1 date.  If there is a need to select multiple dates then try:

      Parts Planned =
      CALCULATE(
      SUM('TableB'[Parts Planned]),
      TREATAS(
      VALUES('TableA'[Date]),
      'TableB'[Date]
      ),
      TREATAS(
      VALUES('TableA'[part number]),
      'TableB'[part number]
      )
      )

      • limonSerga's avatar
        limonSerga
        Regular Visitor
        9 months ago

        Smitho

        Thanks a lot for the support. I was able to make it work in my model! 

    • Smitho's avatar
      Smitho
      Regular Visitor
      9 months ago

      Your relationship is based on date only.  What you actually need is a match on date and Part Number.  An easy solution is to concatenate date and part number in both tables (composite key). You can create a calculated column witth

      Parts Planned =
      LOOKUPVALUE(
      'Table B'[Parts Planned],
      'Table B'[Date], 'Table A'[Date],
      'Table B'[part number], 'Table A'[part number]
      )

       

  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    Super User
    9 months ago

    Hi limonSerga ,

    there might be two reason for you result:

    1. Could it be that an implicit summarrization is defined for "Parts Planned". Please check if
      1. There is a sum sign in front of the your "Parts Planned" column in Data view
        1. You can deactivate that summarization by selecting the column and choose Summarization = "Don't summarize" from "Column tools" menu
      2. In Build view sum is activated as summarization for "Parts Planned"

        1. You can deactivate the summarization directly in the Data window changing the Summmarization entry
    2. You mentioned "However, this is what I get when I try to do sumx calculations". Where do you do a sumx calculation? I wouldn't expect that you need any sum or sumx calculation to get what you want.

    Hope that helps.