Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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]))
@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.
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.
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]
Regards
BTW, everyone, I gave up on this. I'm pursuing a solution in SQL, which seems more straightforward than in PBI.
No Solution!
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:
The difference between importing and using direct query is explained here:
Use DirectQuery in Power BI Desktop
Regards,
Foyiq
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |