Forum Discussion
Measure across two tables
Anonymous
It is highly recommended to have relationships as one-to-many. It's easy to solve, and in fact, if you do not need to keep all rows iin your "Tools_Idefix_Order_Pane" table for some calculation (counting rows or something of that sort), you can simply remove duplicate rows in Power Query in this table and then proceed with loading to the model and creating the one-to-many relationship.
For the sake of this exercise, however, I'm assuming you need to keep the original "Tools_Idefix_Order_Pane" table intact. So we are going to create a new table which will be referenced to this "Tools_Idefix_Order_Pane" (so that any changes in the data source are reflected in the new table), and set it up as a lookup table for your model.
to proceed, go into Power Query and:
1) select your "Tools_Idefix_Order_Pane" table,
2) right click on it and choose "Reference" form the dropdown
this will create a new table (same as your original "Tools_Idefix_Order_Pane" table). Change its name (in my example I've called it Table B lookup).
3) now select the column, and in the Home tab, go to "Remove Rows" and select "Remove Duplicates"
4) Now select "close and Apply" to load the tables into the model and close Power Query.
5) go to the modeling pane and join you GUID column from your lookup table to your other table in a one-to-many relationship
it should look like this:
6) build the visual using the column from this lookup table
Let us know if you encounter any problems!
It's in danish, sorry ;). TableA is actually called Line_StateStatus, TableB is actually called Tools_Idefix_Order_Pane
I can only select many to many when creating the relationship, it won't allow me to select otherwise. Is this why I don't get the expected result?
I create a measure based on your input which gives me the count of Unit=4100 and EM=400.
I then drag GUID from "TableB" to the visual followed by the count.
And get the result as
There is really only 60 results of GUID's from TableA, that fulfills the unit and EM count, and then exists in TableB.
Anonymous
It is highly recommended to have relationships as one-to-many. It's easy to solve, and in fact, if you do not need to keep all rows iin your "Tools_Idefix_Order_Pane" table for some calculation (counting rows or something of that sort), you can simply remove duplicate rows in Power Query in this table and then proceed with loading to the model and creating the one-to-many relationship.
For the sake of this exercise, however, I'm assuming you need to keep the original "Tools_Idefix_Order_Pane" table intact. So we are going to create a new table which will be referenced to this "Tools_Idefix_Order_Pane" (so that any changes in the data source are reflected in the new table), and set it up as a lookup table for your model.
to proceed, go into Power Query and:
1) select your "Tools_Idefix_Order_Pane" table,
2) right click on it and choose "Reference" form the dropdown
this will create a new table (same as your original "Tools_Idefix_Order_Pane" table). Change its name (in my example I've called it Table B lookup).
3) now select the column, and in the Home tab, go to "Remove Rows" and select "Remove Duplicates"
4) Now select "close and Apply" to load the tables into the model and close Power Query.
5) go to the modeling pane and join you GUID column from your lookup table to your other table in a one-to-many relationship
it should look like this:
6) build the visual using the column from this lookup table
Let us know if you encounter any problems!
Removing duplicates and using one to many gives this outcome. I would like to omit the 66 counted rows where the GUID is blank. I guess it's blank because it can't look it up in the TableB lookup.
Anonymous
Correct. You are getting a row with blank GUID because your Table A has more distinct GUID values than in table B.
you can ignore this row by selecting the visual, go to the filter pane and in the dropdown for the GUID values deselect Blank.