cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Matrix with two different tables with no common key

Hello everyone,

I want to create a matrix in PowerBI based on two tables that do not have a common key and I am grateful for any help/ideas.

Table 1 looks like this:

As you can see, it contains a specific hirachy.

Table 2 looks like this (excerpt):

As you can see, each category, subcategory and subsubcategory is a column on its own in Table 2.

The goal is to have all the categories, subcategories, and sub-subcategories from Table 1 with their respective hirachie as rows in the matrix, the customers (C1, C2, etc.) in the columns, and the values from Table 2 as the values. The result should look something like this:

I have created a PowerBI file with examplary data: PowerBi file

Does anyone have a good idea how this solution can be archived? Any help is welcome. 🙂

Best regards,

Fabi

1 ACCEPTED SOLUTION
Community Support

Hi  @FabiNeed ,

Here are the steps you can follow：

1. Enter the power query, select all columns except [Customer], click Unpivot Columns.

Result:

2. Create measure.

``````Flag =
IF(
NOT( ISINSCOPE('Table'[Category])&&ISINSCOPE('Table'[Subcategory])) ,
CALCULATE(SUM('Table2'[Value]),FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Category]))),
IF(
ISINSCOPE('Table'[Category]) &&ISINSCOPE('Table'[Subcategory])&&NOT( ISINSCOPE('Table'[Subsubcategory])),
CALCULATE(SUM('Table2'[Value]),FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Subcategory]))),
IF(
ISINSCOPE('Table'[Category]) &&ISINSCOPE('Table'[Subcategory])&&ISINSCOPE('Table'[Subsubcategory]),
CALCULATE(SUM('Table2'[Value]),FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Subsubcategory])))
,0)))``````

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 REPLIES 3
Community Support

Hi  @FabiNeed ,

Here are the steps you can follow：

1. Enter the power query, select all columns except [Customer], click Unpivot Columns.

Result:

2. Create measure.

``````Flag =
IF(
NOT( ISINSCOPE('Table'[Category])&&ISINSCOPE('Table'[Subcategory])) ,
CALCULATE(SUM('Table2'[Value]),FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Category]))),
IF(
ISINSCOPE('Table'[Category]) &&ISINSCOPE('Table'[Subcategory])&&NOT( ISINSCOPE('Table'[Subsubcategory])),
CALCULATE(SUM('Table2'[Value]),FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Subcategory]))),
IF(
ISINSCOPE('Table'[Category]) &&ISINSCOPE('Table'[Subcategory])&&ISINSCOPE('Table'[Subsubcategory]),
CALCULATE(SUM('Table2'[Value]),FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Subsubcategory])))
,0)))``````

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helper I

I implemented your solution and it works like a charme. However, I do not really really understand, how the filter part is working. Could you please describe it to me in words using this example:

`FILTER(ALL(Table2),'Table2'[Customer]=MAX('Table2'[Customer])&&'Table2'[Attribute]=MAX('Table'[Category]))),`

Especially the parts with the MAX function are interesting to me since I always thought its only possible to compare integer values.

Best,

Fabian

Helper I

This is a perfect solution to the problem. Very well explained. Thank you very much!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors