Measure across two tables

[Anonymous]

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].

[PaulDBrown]

@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!

[Anonymous]

@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:

[Anonymous]

@PaulDBrown 

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.

[PaulDBrown · Accepted 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!

[Anonymous]

@PaulDBrown 

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.

[PaulDBrown]

@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.

[amitchandak]

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

[Anonymous]

@amitchandak 

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.

[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

[Anonymous]

@amitchandak 

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.

[amitchandak]

crossjoin is costly. check on small data first.

[Anonymous]

@amitchandak Thanks, you're right. It's working, although a bit too slow.

[amitchandak]

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