The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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].
Solved! Go to Solution.
@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!
Proud to be a Super User!
Paul on Linkedin.
@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)
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!
Proud to be a Super User!
Paul on Linkedin.
@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.
@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:
Proud to be a Super User!
Paul on Linkedin.
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!
Proud to be a Super User!
Paul on Linkedin.
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.
Proud to be a Super User!
Paul on Linkedin.
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 Linkedin
thank you for the quick reply.
I get a message saying: There's no registered common joined coloumns. 'NATURALINNERJOIN' requires minimum one common joined coloumn.
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
Thank you again
It doesn't give any message, so I guess the code is accepted. But it doesn't give out any value. I can see the cursor is indicating that the measure is in progress.
I've tried to rename TableB[GUID] to TableB[ShortGUID] but same outcome.
crossjoin is costly. check on small data first.
One method is that you add the column in table A
New column in table A= countx(filter(tableB,TableA[GUID] = TableB[GUID]),TableB[GUID])
This can act like a flag now
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
81 | |
62 | |
54 |
User | Count |
---|---|
246 | |
119 | |
114 | |
87 | |
70 |