Forum Discussion

igaca's avatar
igaca
Icon for Helper III rankHelper 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
igaca's avatar
igaca
Icon for Helper III rankHelper III
9 years ago

Here's a screenshot of the (actual) relevant model (below).  The Three Data Sets being linked are colored and labeled.  Balance are shared lookup tables to allow for the tree data sets to talk to one another.  T1 contains "Drawings", T5 contains "Deliverables" (each of which contain multiple drawings), and T9 Contains "Needs IDs" (some of which pertain to multiple Deliverables).  T1 Drawings link to T5 Deliverabels via T3 which contains both and uses T2 as the lookup between it and T1, and uses T4 as the lookup between it and T5 etc...

 

hohlick's avatar
hohlick
Icon for Continued Contributor rankContinued 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
    Icon for Helper III rankHelper 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
      Icon for Continued Contributor rankContinued 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
        Icon for Helper III rankHelper 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