Forum Discussion

igaca's avatar
igaca
Helper III
9 years ago

ADDCOLUMNS and actual values return (e.g. text string)

  Greetings:   I have a question for the Community as follows:   Background:   I used an INTERSECT() to generate a single column table that is a return of distinct values of "Type A" that are ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
hohlick's avatar
hohlick
Continued Contributor
9 years ago
Got it. Do I understand you correctly that for each Value B in Table 2 assigned only one value A? (I.e. pais B-A is unique? )
igaca's avatar
igaca
Helper III
9 years ago

No.  value "Type A" is really is a reference to a "Drawing No." within the real data set I am working with, whereas value "Type B" is a reference to a "Deliverable".  There are multiple drawings for any given deliverable, hence Table 2 (in the hypothetical example provided in the original post) will contains many more "Type A" values than those of "Type B" (many "Type A's" for each "Type B").

 

What I would like to be able to do is create a virtual summary table that reflects all the matching values across various data sets/levels of granularity.

 

Thank you!

  • hohlick's avatar
    hohlick
    Continued Contributor
    9 years ago

    I think this should work:

    // here could be FILTER also in the first row
    CALCULATETABLE (
    SUMMARIZE ( Table2, Table2[Type B Values], Table2[Type A Values] ),
    Table2[Type A Values]
        = CALCULATETABLE (
            INTERSECT ( VALUES ( Table1[Type A Values] ), VALUES ( Table3[Type A Values] ) )
        )
)

    or this (the same in fact):

     

    SUMMARIZECOLUMNS (
    Table2[Type B Values],
    Table2[Type A Values],
    CALCULATETABLE (
        Table2,
        CALCULATETABLE (
            INTERSECT ( VALUES ( Table1[Type A Values] ), VALUES ( Table3[Type A Values] ) )
        )
    )
)
    • igaca's avatar
      igaca
      Helper III
      9 years ago

      Works like a charm and more importanly, I think I understand your approach - bravo and thank you Maxim!

       

      Now let me see if I can complicate things a bit  :)  :

       

      Should there be a need to add another column of values "Type C" which match the returned column of values "Type B" called out in the SUMMARIZE() and filtered in the initial CALCULATETABLE() filter argument, how might one go about that one?

       

      I'm working on it now and will update the post if I resolve the question but I would love to see someone else's (likely better) approach.

       

      Thanks again!

       

      Igor

      • hohlick's avatar
        hohlick
        Continued Contributor
        9 years ago
        What if you just add column C in Summarize? Do not sure in what table it is?