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 have one column (ID #) that is referenced in another column (Link ID #). There is only one instance of the ID #, but multiple instances of the Link ID #. The ID #'s that start with 'MMM', but are null in the Link ID # are because these are summary tasks, so they are basically the 'parent' task. Any Link ID #'s that match that ID #, are the tasks that fall under that 'parent' task.
I have a matrix that has a dropdown of all 'MMM' named ID #'s. I want to create a measure that counts up all the tasks for each ID #. For example, in the below image, MMM34 would have '2' in the count, and if there weren't any tasks under MMM36, the count would be 0. There is also one slicer for users to select (Live or Archive). There would be a seperate count depending on whether the Database column was 'Live' or 'Archive'.
Right now, I have a calculated column like this:
Calculated Column =
CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table,'Table'[Link ID #],'Table'[Database])
This works within the table itself, but since my matrix is showing all the parent ID #'s (The corresponding tasks attached to each ID # are only displayed by drillthrough), not the Link ID #'s, this calculated column doesn't work when I drag it into my matrix.
For example, MMM34 should have '2' displayed in this calculated column, but instead it has nothing displayed there.
How do I go about fixing this or having the column/measure stay consistent with that I need it to calculate, regardless of what I drag into my matrix?
Solved! Go to Solution.
@jabbajuice08 Maybe:
Measure =
VAR __ID = MAX('Table'[ID #])
VAR __Result = COUNTROWS(FILTER('Table 1',[Link ID #] = __ID))
RETURN
__Result
@jabbajuice08 Maybe:
Measure =
VAR __ID = MAX('Table'[ID #])
VAR __Result = COUNTROWS(FILTER('Table 1',[Link ID #] = __ID))
RETURN
__Result
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |