Forum Discussion
FabiNeed
Helper I
Helper IMatrix 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...
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
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
FabiNeedHelper I
Helper IThis is a perfect solution to the problem. Very well explained. Thank you very much!