Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joglidden
Advocate III
Advocate III

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
v-ljerr-msft
Employee
Employee

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

Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

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

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

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

 

No Solution!

Anonymous
Not applicable

I also need a calculated column using direct query but nothing seems to work, I'm trying to use an if: 

Date_a = IF(fct_advertiser_events[fk_advertiser]=894,fct_advertiser_events[time_utc],"other")

did someone find a viable solution?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.