Summarize over columns from Different Tables

[blytonpereira]

Hello

I would like to use the SUMX function with Summarize over columns from different tables.

I have a table named "Region" with a single column also named "Region".

I have a second table named "P2" and  a single column named "P2".

For summarize in powerbi we are only allowed to use one table. Is there any way to tweak the equation to make it work for two tables like below ?

The measure should be SOMETHING LIKE

Equation= SUMX(SUMMARIZE(Table1(Column1),Table2(Column2)),MYMEASURE

[v-shex-msft]

HI @blytonpereira,

It will be help if you provide some details information.

In my opinion, I'd like to suggest you create a variable table to add new column to expression table to store value from another table based on relationship.  Then you can simply to summarize and calculate on variable table.

For example:

Measure =
VAR summary =
    SUMMARIZE (
        ADDCOLUMNS ( Table1, "T2Col", RELATED ( Table2[Column2] ) ),
        Table[Column],
        [T2Col]
    )
RETURN
    SUMX ( summary, [Measure2] )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi  v-shex-msft

Im trying to use the measure to distinct count records in my data but it doesnt work.

I have 3 tables: 

Date Table --> Summarized by Month

Card Type Name --> Counted by Card Type Name

Station Sales Prepaid [ Account Number] -- > Field to be counted

[blytonpereira]

@v-shex-msft

My raw original tables look like below

Table name:Black                                                            Table Name: White

P2     Region     Country       Value                                  Plan     Regionx   Countryx    Result

23x   ASPAC     Nord            2                                        7x         NA           JAP               2  

23x   EMEA       EAST            3                                        15x       NA           JAP               2  

18x   ASPAC     Nord            2                                        11x       NA           JAP               2  

17x   NA           JAP               2                                        11x       NA           JAP               2  

In my design I have used PowerQuery to

1. Create new table view and Group Table Black by P2 and Region

2. Create another new table view and Group Table White by P2 and Region

3. Append both 1. and 2 in another new table, this will be my brideg table connecting my raw data Black and White

4. Remove duplicates from 3,. All of these steps effecively will create a full outer join which is what I need.

5. Add a Third column and concatanate columns 1 and 2 to create a unique key

6. Create a one to many reationship from this unique key in this Bridge table to my orignal Black and White tables. (I also had to create the concatanated column in my original Black and white tables to make this link)

7. Now I  can write a Summarize DAX measure over the P2 and Region columns in the bridge table.

8. Lastly create another table of unique P2 from an APPEND of table Black and White. This is linked by a one to many relationship with P2 in my Black table and Plan in my white table.

As can be seen this is very heavy on the CPU processor since there are about 10 million rows.

I would like to understand if your way is infact simpler ? It does seem very light as you are only using a DAX function to summarize across 2 tables rather than create this bridge table which I have done ?

If your is indeed much lighter can you pls rewrite your DAX measure to match the column and table naming I have provided?

Why I made the bridge table ?

I wanted to create the following measure

ABS Error P2/Country = SUMX(SUMMARIZE('Bridge_table','Black'[P2_Region],'White'[PLan_Regionx]),ABS([Sum of Error]))

 Then in my table visualization I would like to display the following

P2(this is the result of Step 😎             Value (from Black table)              Result (from White)       ABS Error (measure above)

[v-shex-msft]

Hi @blytonpereira,

>>If your is indeed much lighter can you pls rewrite your DAX measure to match the column and table naming I have provided?

Nope, they also need to be calculated in variable tables.

I'd like to suggest you create bridge table on date view side.

Steps:

1. Add calculated column PR to raw tables based on their 'plan', 'region' fields.

PR = Black[P2]&"-"&Black[Region]
PR = White[Plan]&"-"&White[Regionx]

2. Create a bridge table with union records.

Bridge =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT ( UNION ( VALUES ( White[Plan] ), VALUES ( Black[P2] ) ) ),
        DISTINCT ( UNION ( VALUES ( Black[Region] ), VALUES ( White[Regionx] ) ) )
    ),
    "PR", [Plan] & "-" & [Region]
)

3. Create relationship between two table and bridge table.

After these you can use bridge table fields and other table value column to create graph or crete measures.

Regards,

Xiaoxin Sheng

[AkhilAshok]

Does these 2 tables, Region and P2, relate to a common fact table? If yes, then you have to SUMMARIZE over the common fact table to which these 2 tables are linked to:

Measure =
SUMX ( SUMMARIZE ( Fact, Region[Region], P2[P2] ), [Measure] )