cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
igaca
Helper III
Helper III

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 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

59270
65223
1355155
155a321
161a1143
197231
213168
21717
717
8318
108105
12553
1648324
17553
19325
20223
2919
903157
913157
923154
933168
943157
953168
963157
1025155
1035155
1045155
1055155
1075155
1611143
17011
1752250
17625
17722
17822
1935281
206321
6223
44370
45270
48270
631143
643169
69114
7047
736275
746275
756275
796275
876283
885281
140a5281
141325
1425281
1463268
15028
15219
153325
15526
15919
11 REPLIES 11
hohlick
MVP

Is Table1, Table2 and Table3 linked?

Maxim Zelensky
excel-inside.pro

Yes, they are linked.

 

 

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...

 

Model screenshot.png

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? )
Maxim Zelensky
excel-inside.pro

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!

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] ) )
        )
    )
)
Maxim Zelensky
excel-inside.pro

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

@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

What if you just add column C in Summarize? Do not sure in what table it is?
Maxim Zelensky
excel-inside.pro

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) 

@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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors