cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndreT Regular Visitor
Regular Visitor

What's wrong with this formula?

Hi,

I just wanted to check what is wrong with this formula?

 

Closed with Task = OR(CALCULATE(DISTINCTCOUNT(Task[Job ID (Task) ]),'Task'[Job.Status__c]="Closed"), CALCULATE(DISTINCTCOUNT(Task[Job ID (Task) ]),'Task'[Job.Status__c]="Paymnet_Entry"))

Want to count jobs which either are closed or are in payment_entry status.

 

After entering this forumla I don't get any results at all.

1 ACCEPTED SOLUTION

Accepted Solutions
GabrielSantos Regular Visitor
Regular Visitor

Re: What's wrong with this formula?

Ah, got ya.

 

I believe this will work:

 

DistinctCount.Open.PaymentEntry = CALCULATE(
                                             DISTINCTCOUNT( Task2[Job_ID] ),
                                                    FILTER( Task2 ,
                                                               Task2[Job_Status] = "Closed"
                                                           ||  Task2[Job_Status] = "Payment Entry")
)
5 REPLIES 5
GabrielSantos Regular Visitor
Regular Visitor

Re: What's wrong with this formula?

Hey there!

 

I believe you'll be able to achieve your desired result with a simpler code. 

 

Try this:

 

Count Closed.PaymentEntry = COUNTROWS(
                                       FILTER( Task ,
                                                   Task[Job_Status] = "Closed"
                                               ||  Task[Job_Status] = "Payment Entry" )
)

In short, we use the countrows function to count the rows of a table that we filter to only include rows with "Closed" or "Payment Entry" job statuses. The || is the "or" operator.

 

 

AndreT Regular Visitor
Regular Visitor

Re: What's wrong with this formula?

Hey,
It would work only if all tasks had unique job id. Because there are jobs with mulitple taks I have to use distinct count.
GabrielSantos Regular Visitor
Regular Visitor

Re: What's wrong with this formula?

Ah, got ya.

 

I believe this will work:

 

DistinctCount.Open.PaymentEntry = CALCULATE(
                                             DISTINCTCOUNT( Task2[Job_ID] ),
                                                    FILTER( Task2 ,
                                                               Task2[Job_Status] = "Closed"
                                                           ||  Task2[Job_Status] = "Payment Entry")
)
v-huizhn-msft Super Contributor
Super Contributor

Re: What's wrong with this formula?

Hi @AndreT,

The formula @GabrielSantos posted is right. Please try it and mark the right reply as answer if you have resolved your issue.

Best Regards,
Angelia

AndreT Regular Visitor
Regular Visitor

Re: What's wrong with this formula?

Thank you, it worked.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 43 members 1,151 guests
Please welcome our newest community members: