Calculating results based on two Columns and displaying result on each row

[Brett007]

I am trying to determine if a Project has associated Actions that are open. 
This will be used in filtering the page.

I want to show all of the Projects with no Open Actions.  (A Measure cannot be used as a filter for a page)

If it has no open actions I want it to say "No Open Actions"  

Power BI Example  

I added a Column that shows the result that I would like "Newley Created Column in dataset Example"

I thought I could use an IF Statement with a filter, but nothing seems to work

IF(Project Status = "Open" && Action Status = "Open"),
FILTER('Table','Table'[ID] = EARLIER('Table','Table'[ID])

I always get the result for that row and I want the result for that Project.  As shown in the example column.

I have also tried:

CALCULATETABLE(

FILTER('Table','Table'[Id] = EARLIER('Table'[Id]),

IF('Table'[Action Status] = "Open",1,0)))

That does not work at all.

Thank you for your help.

[Ashish_Mathur]

Hi,

Please show the exact result that you are expecting.

[Brett007]

I added a dataset with the Column results that I would like.  Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Your end result seems inconsistent to me.  I do not understand the logic you have used to populate then entries in the "Newley Created Column in dataset Example" column.  Anyways, write this calculated column formula in the Issues_sheet table

Column = if(CALCULATE(COUNTROWS(Issues_Sheet),FILTER(Issues_Sheet,Issues_Sheet[Project]=EARLIER(Issues_Sheet[Project])&&Issues_Sheet[Action Status]="Open"))>0,"Open Actions","No open Actions")

If my results are wrong, then restate your question in simple English clearly specifying the conditions.

[Brett007]

Thank you for being able to interpret my difficult to decypher problem.  If you were to reword this formula what would you call it?  Thanks.

[Ashish_Mathur]

You are welcome.  Does my formula show the correct result?

[Brett007]

Yes it does.

[selimovd]

Hey @Brett007 ,

check the following measure, that will tell you the amount of open tasks by project:

Amount Open Projects = 
VAR vProjectTable = ADDCOLUMNS (
    VALUES ( MyTable[ID] ),
    "@OpenTasks", CALCULATE ( COUNTROWS ( MyTable ), MyTable[ID Status] = "Open" && MyTable[Task Status] = "Open" )
)

RETURN
SUMX(vProjectTable, [@OpenTasks])

Would that work for you?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Brett007]

That gives me a numerical value.  How do I use that numerical value of count of open tasks per Project to show in a matrix next to the project name?

Thank you