cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jabbajuice08
Frequent Visitor

Need filters/numbers to stay the same in measure/calculated column

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

 

jabbajuice08_1-1669908969592.png

 

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?

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@jabbajuice08 Maybe:

Measure = 
  VAR __ID = MAX('Table'[ID #])
  VAR __Result = COUNTROWS(FILTER('Table 1',[Link ID #] = __ID))
RETURN
  __Result

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

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

@jabbajuice08 Maybe:

Measure = 
  VAR __ID = MAX('Table'[ID #])
  VAR __Result = COUNTROWS(FILTER('Table 1',[Link ID #] = __ID))
RETURN
  __Result

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.