Forum Discussion

FabiNeed's avatar
FabiNeed
Icon for Helper I rankHelper I
4 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • FabiNeed's avatar
      FabiNeed
      Icon for Helper I rankHelper I
      4 years ago

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

    • FabiNeed's avatar
      FabiNeed
      Icon for Helper I rankHelper I
      4 years ago

      Anonymous 

      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