Count of a column based on another filtered column.

[Anonymous]

I tried to search but couldn't find an apt solution here so i'm posting. I assume there must be a simple solution to this problem and my solution to the problem is having optimisation issues. This is kind of the famous Manager- Employee self join problem.

In Agile environment we have Portfolios with Initiatives associated to them. Some initiatives have Planviews associated with them. What i need is the count of Portfolios that are associated with Initiatives with a PlanviewID.

Please find the pic below for example:

Here we have initiatives I1, I3, I4 with Planviews. We need the count of Portfolio (column 1) which have Initiatives I1, I3, I4 (column 4). 

Here 4 portfolios that are associated to I2 and I5 but those initiatives dont have a planiewID so our final count is 6.

Note : This is just an excerpt and Portfolios can be repetitive based on some other items.

I created one calculated table

=CALCULATETABLE(VALUES(Agile[portfolio_id]),FILTER(Agile, Agile[Type] = "Initiative" && Agile[planview] <> BLANK()))

I got all the initiatives with Planview IDs. All i need now is occurances of these initiatives for distinct Portfolios.

P.S. - If its been solved here before kindly direct me to the page.

[PattemManohar]

@Anonymous Please try this as a new Measure.

Test31 = 
VAR _ValidInit = SUMMARIZECOLUMNS(Test31[Portfolio],FILTER(Test31,Test31[Planview]<>BLANK()))
VAR _Filter = FILTER(Test31,Test31[Initiative] IN _ValidInit)
RETURN COUNTROWS(_Filter)

Note - It will be great and helpful if you can post the sample data in copiable format.

[Anonymous]

Since Portfolio's aren't repeated you could just add PlanView as a Card and change the Calculation to a Count from the "Fields" section. Then edit the Format, Disable the Category Label. Enable the Title. Then call it Count of Portfolio

Or create a measure like below and use that for the Card

Count of Portfolio = COUNT(Table2[PlanView])

[Anonymous]

@Anonymous Unfortunately this was just an excerpt and the Portfolio IDs can be repetitive based on some other items available. However Distinctcount won't give the right count with your DAX since it gives the output like :

(Portfolios with Initiatives) with PlanviewID

instead of 

Portfolio with (Initiatives with PlanviewID)

They both give different count.

I created one calculated table

=CALCULATETABLE(VALUES(Agile[portfolio_id]),FILTER(Agile, Agile[Type] = "Initiative" && Agile[plan_view_id] <> BLANK()))

I got all the initiatives with Planview IDs. All i need now is occurances of these initiatives for distinct Portfolios.