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

[igaca]

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 common between Table 1 and Table 3.  Now I wish to generate an additional (extended) column using ADDCOLUMNS() to return all values of "Type B" from Table 2, which match the "Type A" values returned in the first column via INTERSECT() statement.  Note that Table 2 contains "Type A" and "Type B" values (so there is a granularity transition there.)

Since ADDCOLUMNS() is an iterator, I am able to wrap the ADDCOLUMNS() expression argument in CALCULATETABLE which in turn is wrapped by an Iterator function (e.g. COUNTROWS() to return e.g. an accurate count of "Type B" values matching each "Type A" value.  But, how might one go about returning the mathing "Type B" values (text) as opposed to -in this example- a count thereof?

Example Measure =

Part 1 - return single column table containing common "Type A" values btw Tables 1 and 3:

INTERSECT(DISTINCT(Table1[Type A Values]),DISTINCT(Table 3[Type A Values]))

Part 2 - return count of mathing "Type B" values (for each "Type A" value in the above resulting column):

ADDCOLUMNS(Part 1 expression),"Cnt of Matching Type B Values",COUNTROWS(CALCULATETABLE(DISTINCT(Table 2[Type B Values])

Entire expression:

ADDCOLUMNS(INTERSECT(DISTINCT(Table1[Type A Values]),DISTINCT(Table 3[Type B Values])),"Cnt of Matching Type B Values",COUNTROWS(CALCULATETABLE(DISTINCT(Table 2[Type B Values])

Again, the second column in the resulting virtual table ("Cnt of Mathing Type B Values") populates accurate counts of matching "Type B" values.  The question is how might one go about actually returning all of the mathing "Type B" values -for each "Type A" value, as opposed to merely their count?

Notes:

* I udnerstand same result for part 1 could be obtained via CALCULATETABLE(DISTINCT(Table 2[Type A Values]),RELATEDTABLE(Table 1),RELATEDTABLE(Table 2)) - let me know if you think this is a better solution and the reasons why.

* My problem in reality is a bit more complex in that it involves additional transitions in data granularity via additional lookup tables and fact tables but if I can get assistance with resolving the above example - that is how does one get ADDCOLUMNS() to return actual values which correspond to an adjacent column generated by it's <Table> argument- I should be able to apply the concept more broadly.

* I've tried IF(COUNTROWS(DISTINCT(Table 2[Type B Values]))>1,BLANK(),DISTINCT(Table 2[Type B Values])) but no cigar...

Let me know if you have any thoughts.  Thanks in advance!

Igor

Sample from actual data set (involves an additional set of values - say "Type C")

Needs ItemMathing EDLsMatching Rev.0 Dwgs

59	2	70
65	2	23
135	5	155
155a	3	21
161a	1	143
197	2	31
213	1	68
217	1	7
7	1	7
8	3	18
10	8	105
12	5	53
16	48	324
17	5	53
19	3	25
20	2	23
29	1	9
90	3	157
91	3	157
92	3	154
93	3	168
94	3	157
95	3	168
96	3	157
102	5	155
103	5	155
104	5	155
105	5	155
107	5	155
161	1	143
170	1	1
175	2	250
176	2	5
177	2	2
178	2	2
193	5	281
206	3	21
6	2	23
44	3	70
45	2	70
48	2	70
63	1	143
64	3	169
69	1	14
70	4	7
73	6	275
74	6	275
75	6	275
79	6	275
87	6	283
88	5	281
140a	5	281
141	3	25
142	5	281
146	3	268
150	2	8
152	1	9
153	3	25
155	2	6
159	1	9

[hohlick]

Is Table1, Table2 and Table3 linked?

[igaca]

Yes, they are linked.

[igaca]

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]

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]

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]

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]

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

[v-haibl-msft]

@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

[hohlick]

What if you just add column C in Summarize? Do not sure in what table it is?

[igaca]

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]

@igaca

I’m not quite clear about your current problem here. Could you please give some table samples with some data in them, and describe the expected output?

Best Regards,

Herbert