Forum Discussion
Helper ICombining data into one bar chart
Sure thing.
The basic idea is that we need to unpivot both tables and then build a relationship between them.
To unpivot the tables go into Power Query via the Transform data icon
On Table1 select the Product column and then from the Transform portion of the ribbon select Unpivot Other Columns.
I renamed the resulting "Attribute" column as "Category"
Do the same for Table2 selecting the "Code" column and unpivoting the other columns
In this case I rename the "Attribute" column to "Product" to match the column name in Table1
Close and Apply these changes to go back into the BI screen.
You can now set a Many to Many relationship between the two tables using the product columns. It will filter in both directions.
The measure I used was;
Both Y =CALCULATE(COUNT(Table1[Category]),FILTER(Table2,Table2[Value]="Y"),FILTER(Table1,Table1[Value]="Y"))Hope this helps.
Super UserSure thing.
The basic idea is that we need to unpivot both tables and then build a relationship between them.
To unpivot the tables go into Power Query via the Transform data icon
On Table1 select the Product column and then from the Transform portion of the ribbon select Unpivot Other Columns.
I renamed the resulting "Attribute" column as "Category"
Do the same for Table2 selecting the "Code" column and unpivoting the other columns
In this case I rename the "Attribute" column to "Product" to match the column name in Table1
Close and Apply these changes to go back into the BI screen.
You can now set a Many to Many relationship between the two tables using the product columns. It will filter in both directions.
The measure I used was;