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

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