Forum Discussion
Measure across two tables
Hi
Noob here.
I'm trying to count number of rows in one table, based on a comparison that involves a second table.
I've been reading about innerjoin and relatedtables, but I don't understand it yet, and would like a specific suggestion on how to solve this.
TableA - coloumns GUID, Unit & EM
TableB - coloumn GUID
Count number of rows in table A where Unit= 4100 and EM = 400, and where tableA[GUID] = TableB[Guid].
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!
14 Replies
- amitchandak
Super User
Try
countx(filter(NATURALINNERJOIN(TableA,TableB ),TableA[Unit]= 4100 && TableA[EM] = 400),TableA[GUID])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedinthank you for the quick reply.
I get a message saying: There's no registered common joined coloumns. 'NATURALINNERJOIN' requires minimum one common joined coloumn.
- amitchandak
Try
countx(filter(crossjoin(TableA,TableB ),TableA[GUID] = TableB[GUID] && TableA[Unit]= 4100 && TableA[EM] = 400),TableA[GUID])
But this might give issues with common column names. Some rename might be needed
PaulDBrownCommunity Champion
Anonymous
One way is to set up your model with your Table B as your lookup/dimension table for the GUID.
(Using some sample data for illustrations purposes)Two tables
Create a relationship bewteen both tables in the modeling pane by joining the GUID fields (I'm using my Item column) in both tables:
Based on my example, I'm counting rows where Channel = "A" and REF = "3":
Countrows where Channel is A and REF is 3 = CALCULATE(COUNTROWS('Table A'); FILTER('Table A'; 'Table A'[Channel] = "A" && 'Table A'[REF] = "3"))Now create a table/matrix using the GUID field (Item in my case) from Table B as rows, and include your measure and you get this (I've added another table called "Checking Results" so you can see what the measure is delivering):
Hope this helps!
PaulDBrown Thank you.
I'm hitting the wall when you mention "Ref = 3". I don't understand how to rewrite that for my use.
I'm trying Count = CALCULATE(COUNTROWS('TableA');FILTER('TableA';'TableA[Unit]=4100 && TableA[EM]=400 && TableA???? but I don't understand how to point to the join/link.
- PaulDBrown
Anonymous
You don't have to reference the link. Once you have set up the model as above, with the relationship between both tables as a one-to-many from table b to table a (joining the GUID fields), you use the GUID field from table b in your visual for your rows, and add the measure.
In your case, the measure is:
Count = CALCULATE(COUNTROWS('TableA'); FILTER('TableA'; 'TableA[Unit]=4100 && TableA[EM]=400))Here is a visual representation of how to set it up:
