cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joglidden Member
Member

Need a calculated column that counts rows in a related table.. nothing I try works

I am trying to create a column (it needs to be a column, not a measure, so that I can filter on it) that basically counts the number of related records in a related table for each record. This would be ideal:

 

Trend_findings = COUNTROWS(RELATEDTABLE(GIS_RA_finding[GIS_Finding_ID]))

 

I have established the relationship between the tables, and I can basically reproduce this information in a Matrix table (see below). However nothing I try in Desktop or "Edit Queries" works. I just get errors. I have seen examples of this working in PowerPivot, but none of the examples work in PBI desktop. 

 

If anyone has a suggestion, I would greatly appreciate it. 

 

1.JPG

10 REPLIES 10
Highlighted
Super User IV
Super User IV

Re: Need a calculated column that counts rows in a related table.. nothing I try works

If you are trying to do this in Edit Queries, then it is not going to work. The Query Editor uses M code and what you have there is DAX. Go into your data model and go to the Modeling tab and create your calculated column there. This should work:

 

Column = CALCULATE(COUNTROWS(GIS_RA_finding),RELATEDTABLE(GIS_RA_finding)) 

Put this column in your related table (the one related to GIS_RA_finding) where you are trying to create Trend_findings.

 

I also confirmed that this should work as well:

 

Column = COUNTROWS(RELATEDTABLE(GIS_RA_finding[GIS_Finding_ID]))

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Microsoft v-ljerr-msft
Microsoft

Re: Need a calculated column that counts rows in a related table.. nothing I try works

Hi @joglidden,



I am trying to create a column (it needs to be a column, not a measure, so that I can filter on it) that basically counts the number of related records in a related table for each record. This would be ideal:

 

Trend_findings = COUNTROWS(RELATEDTABLE(GIS_RA_finding[GIS_Finding_ID]))


According to your description, the formula you provided above should work if there is a proper relationship between the two tables.


However nothing I try in Desktop or "Edit Queries" works. I just get errors. I have seen examples of this working in PowerPivot, but none of the examples work in PBI desktop. 


Could you post the detailed error message here?

 

In addition, could you post your table structures with some sample data which can reproduce the issue, so that we can better assist on it?

 

Regards

joglidden Member
Member

Re: Need a calculated column that counts rows in a related table.. nothing I try works

@Greg_DecklerI'm still not finding a way to get this to work. However, I'm not finding a 'data model' tab either. This report is using Direct Query, and I only get the 'Report' tab and the 'Relationships' tab on the left side. Is it not possible to this in Direct Query?

 

joglidden Member
Member

Re: Need a calculated column that counts rows in a related table.. nothing I try works

@Greg_Deckler, also when I check 'allow unrestricted measures' and then restart PBI, I get this message: "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models." That seems to confirm this is not possible in direct query models. 

foyiq Regular Visitor
Regular Visitor

Re: Need a calculated column that counts rows in a related table.. nothing I try works

@joglidden What they mean by 'data modeling tab' should actually be present in any PBI Desktop file, even a blank one.

02.06.png

 

As for whether it is possible to have your task accomplished in direct query mode, my thought is that it is not.
Query mode does not deal with relationships between different tables, it is used to reshape each source to generate a separate output. So, probably, you might need to use DAX.

joglidden Member
Member

Re: Need a calculated column that counts rows in a related table.. nothing I try works

@foyiq, I don't have that tab. I only have the other two. Is this the case with all direct query reports?

foyiq Regular Visitor
Regular Visitor

Re: Need a calculated column that counts rows in a related table.. nothing I try works

I got that now. This is because you are using direct query. refer to this thread:

Data tab not showing up

 

The difference between importing and using direct query is explained here:

Use DirectQuery in Power BI Desktop

 

Regards,

Foyiq

Microsoft v-ljerr-msft
Microsoft

Re: Need a calculated column that counts rows in a related table.. nothing I try works

Hi @joglidden,

 

If you're using the DirectQuery, I don't think there is an easy to add this calculate column using DAX or Power Query.

 

In this scenario, I would suggest you try using SQL Statement option to import a new query which contains the ID, and the count of each ID in another table. Smiley Happy

SELECT T1.[ID], COUNT(T2.[GIS_Finding_ID]) AS Trend_findings

FROM  TABLE1 T1 INNER JOIN GIS_RA_finding T2 ON 

T1.[ID] = T2.[GIS_Finding_ID]

GROUP BY [ID]

t1.PNG

 

Regards

joglidden Member
Member

Re: Need a calculated column that counts rows in a related table.. nothing I try works

BTW, everyone, I gave up on this. I'm pursuing a solution in SQL, which seems more straightforward than in PBI. 

 

No Solution!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors