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 need to calulate all rows with the same value in a column. I see someone posted this answer:
new_calculated = COUNTROWS(FILTER('current table',
(EARLIER('current table'[3rd_column]) = 'current table'[3rd_column])))
That's great and it works but it's adding up the rows for the ENTIRE table, which I don't want. My report is filtered to just have part of the table shown (I only selected rows where a certain column = a certain value). I just want the new calculated column to calculate for the rows shown, not for the entire table.
Hope that makes sense, and any help is appreciated! 🙂
Solved! Go to Solution.
Hi @zamboni1199,
It seems that you may filter the report with the date slicer.
If it is your case, you could create the calculated column with the measure below.
Column = COUNTROWS ( FILTER ( 'Table1', EARLIER ( 'Table1'[Employee] ) = 'Table1'[Employee] && 'Table1'[Date].[Month] = EARLIER ( Table1[Date].[Month] ) ) )
Here is the result output.
If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
@zamboni1199 Any sample data and expected output will be really helpful to provide the solution quicker and accurate....
Proud to be a PBI Community Champion
Yes, thank you, here you go:
Here is the filtered data in my report:
Created On Employee Number of Occurrences
1/1/2018 John Doe 43
1/10/18 Jill Smith 17
1/21/18 John Doe 43
The number of occurrences should read 2 for John Doe and 1 for Jill Smith because I'm only showing data that was Created in January of 2018. But the Number of Occurrences is showing the total times John Doe and Jill Smith are listed in the table (from January 2018 through the current date). I just need the Number of Occurrences to match what's showing on my filtered report. Thanks.
Hi @zamboni1199,
It seems that you may filter the report with the date slicer.
If it is your case, you could create the calculated column with the measure below.
Column = COUNTROWS ( FILTER ( 'Table1', EARLIER ( 'Table1'[Employee] ) = 'Table1'[Employee] && 'Table1'[Date].[Month] = EARLIER ( Table1[Date].[Month] ) ) )
Here is the result output.
If you still need help, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Thank you, that was great!
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |