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
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?
      • igaca's avatar
        igaca
        Helper III
        9 years ago

        The issue is that SUMMARIZE requires the initial <table> argument and there is no single table within the entire model that contains / correlates all values types ("A" --> "B" --> "C").  The other issue is the relationship type/flow from tables.  For example, the table referenced within SUMMARIZE is table 3 - "T3 EDL to DWGS"- and value Type "C" columns are located in table 7 ("T7 EDL to NEEDS Table") which correlates Type "B" and "C".  I've tried to use RELATEDTABLE() but it will not work due to multiple changes in table relationships (many-to-one, then one-to-many, then many-to-one, and lastly one-to-many again as T7 is reached) 

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      igaca

       

      It seems that there are Type A Values in Table1, Type A Values and Type B Values in Table2, Type A Values in Table3. Where does the Type C Values come from?

       

      Best Regards,

      Herbert