cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brett007
Helper II
Helper II

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

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.

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User III
Super User III

Hi,

Please show the exact result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes it does.

selimovd
Community Champion
Community Champion

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
 

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors