How to calculate multiple rows for a condition

[Surfingjoe]

We have data being provided from software that gives the status on a workflow. It includes status of workflow steps previously completed.

I’m struggling to model the data in SQL and/or create DAX calculations that determine how many orders are in what part of a workflow process.

Need to determine the following

• how many orders have been created and verification process has not started?
• How many orders where the verification process has completed
• How many orders are waiting to be authorized
• How many orders completed authorization process and are ready to schedule
• how many orders have been scheduled
• how many orders has been scheduled and have completed a treatment?

What I’m failing to grasp is how to evaluate multiple rows per order and determine the order’s position in the work flow and then count it in the correct bucket

To explain, the report lists all of the conditions that have completed for each order and multiple conditions can occur on the same date.

If I calculate to include or exclude a Status condition against an order, I’m flummoxed.

For example, exclude all orders that do not have “"ready to schedule", that is true of all orders because all orders have at least one or more rows where status is not “ready to schedule”

See attachedTemp Order Report.xlsx (262.9 KB)

[sam.mckay]

I understand this can be confusing and quite a lot of people get caught on this.

It’s all about dealing with multiple dates in your fact table, like you are here with the workflow process.

The key here is a two-step process. First, you need to get the data model correct then, use the right DAX measure. It’s not too difficult once you know the technique here.

The technique is detailed here. It’s not the exact scenario you have but the technique of dealing with multiple dates is exactly the same.

Sales and Orders in Progress

The pattern is called ‘Events In Progress’, which is essentially what you are looking for here.

You must use the inactive relationships like the below.

You may have multiple of these if you have lots of dates.

Then you need to use this pattern of formula to get all the calculations you require

Again you might have to have many of similar formulas that look like this as it looks like you have lot’s of different calculations across many different dates.

See how you go with these techniques.

Here’s some other questions that are similar to this one that I’ve answered previously.

Employee Headcount

Sure happy to look into this but need to know more. There’s a lot at play here so need to understand the data within the tables, how you setup your data model including the relationships you have and also what is the current context of the calculation. Unfortunately with DAX it’s never a simple as just reviewing the formula by itself, there always more to it. If you want to add a demo model here with the scenario then that will speed up assistance. Chrs Just as a side note on this as well a…

Staff Population

Have you had a chance to work through this workshop here as this covers many of these calculations for HR data. As with anything here the data matters a lot. So with your data you have to manage multiple dates…very doable. This is where the ‘events is progress’ pattern comes into play. See below for the exact technique to use from a modelling and DAX formula perspective. This is a different scenario exampled above but the technique is exactly the same. I’ve worked on something briefly h…

Show events based on opening date as well as closing date

This is the exact technique you want to review with this. See below - It’s called the Events In Progress technique Follow the technique here exactly and you’ll see how you can calculated both of these. The key is to understand how the USERELATIONSHIP function works. This is a key function that has some very useful usages. So a great one to review in-depth.

[Surfingjoe]

Thank You for the response, it did help me focus on time based data that can be extracted.

However, I’m still struggling with creating formula based on multiple rows within a column. I’m trying to determine how many orders have not made it to “ready to schedule” status. Technically every order has a status that is not “ready to schedule”. So while I can find and count all orders that have that status, I’m struggling to find and count all orders between created and “ready to schedule”. Max date date stamp doesn’t help, because all of the changes in status can happen on the same day.

I’m attaching my Power BI file to show what I’ve come up with so far.another orders report.pbix (221.7 KB)

[sam.mckay]

Ok few things after reviewing your model.

You should look to merge the key details from the Holiday Schedule table into your date table. There’s no need to have this separate. Really the key details to place into columns in the date table would be; is any day a holiday, what holiday it is…that’s all you need - this can all be done in the query editor.

Also this DatePeriod table is totally unnecessary. All of this work for this should be done using Dynamic Segmentation/Grouping techniques.

Here’s some details around grouping techniques.

Dynamic Grouping via Support Tables

Then as per above I mentioned you want all your relationships to all the dates in your fact table to be inactive.

Also make sure these are date formats not date/time

All of the below are also not required

All of these calculations can and should be completed dynamically in DAX formulas. I recommend to (almost) never create any calculated columns in your fact tables. They just aren’t necessary.

This is how to model should look

Now the key with this inactive relationships is that if you want to turn any of them on you want to be using USERELATIONSHIP - it looks like you know this already which is good.

This is the technique to use below

After looking through your formula though you still haven’t used the ‘Events In Progress’ patterns I recommended above and this is how you solve this problem from here.

Sales and Orders in Progress

Orders Between Dates = 
CALCULATE( [Total Oders],
    FILTER( VALUES( Query1[Created Date] ), Query1[Created Date] <= MAX( Dates[Date] ) ),
    FILTER( VALUES( Query1[Expiration Date] ), Query1[Expiration Date] >= MIN( Dates[Date] ) ) )

I might not have the exact dates here but hopefully by seeing this technique you’ll know where to fill things in.

I’ve attached the updated file here.
another orders report.pbix (244.7 KB)

Thanks
Sam

[Surfingjoe]

So I’ve created an even simpler Power BI example to illustrate what I’m trying to solve. In the attached Power BI sample is a Measure that has a warning because it is not completed.

The measure is:

Not Ready to Schedule = 
    CALCULATE(DISTINCTCOUNT(Orders[Order ID]),
        FILTER(Orders,NOT(Orders[Order ID] in 
     -- _How do I get a list of Order ID's that do not have a column status of "Ready to Schedule" in any Row associated to that Order ID_

The Dates are irrelevant because the change of status can cause multiple rows of different status on the same date and the same time (its just the stupid way the software works). When a status changes, the “Requested At” date and time changes, but I can’t even go by “Requested At” because multiple rows can be created at the exact same time and date with each row having a different value in the column “Status”.

It’s easy to count “order id” where any row = “Ready to Schedule”, but how do I find the reverse, how do I find and count “Order ID” where all rows associated to that “Order ID” do not have “ready to schedule”.

I can do this with SQL as follows:

_SELECT distinct_
_count([Order ID]) as 'Count Orders not ready'_
_FROM [dbo].[WeInfuseOrderStatusesReport]_
_where [Order ID] not in_
_(select distinct os.[Order ID] from [dbo].[WeInfuseOrderStatusesReport] OS where OS.Status= 'Ready To Schedule')_ 

I’m struggling to be able to do the same in Power BIOrders report Sample.pbix (133.7 KB)

[sam.mckay]

Ok sure.

It seems that my understanding of the initial question wasn’t quite what you needed, but this honestly isn’t too difficult. It just requires an understanding of a few nuances to Power BI. One being the ‘initial context’ and then iterating functions in DAX.

First I just attempting to see in a table how many orders for each Order ID.

So now basically all we need to do is iterate through each of these rows for each order ID and run some logic.

First I calculated this just out of interest…

Rows w/Ready To Schedule = 
CALCULATE( [Count of Rows],
    FILTER( Orders, Orders[Status] = "Ready To Schedule" ) )

Then this

Rows w/no Ready To Schedule =
VAR ReadyToSchedule = CALCULATE( [Count of Rows],FILTER( Orders, Orders[Status] = "Ready To Schedule" ) )

RETURN
IF( ReadyToSchedule > 0, BLANK(), 1 )

Now if you want this is a different context like the table you made up, it’s a simple variation on this.

Not Ready to Schedule = 
CALCULATE( COUNTROWS( VALUES( Orders[Order ID] ) ),
    FILTER( VALUES( Orders[Order ID] ), [Rows w/no Ready To Schedule] = 1 ) )

Looks like it works for me now.

See how you go with this.
Orders report Sample.pbix (138.6 KB)

Sam

[Surfingjoe]

Thank You, It works great in providing the count of orders not ready and as well I can apply the same method to orders ready but have not completed treatment etc. I’ve read in books about count rows, but never really grasped why I would use the count rows formula. I’m so glad to have gone through this dilemma, because now its crystal clear that using count rows with a filter gives me the count I was beating my head against the wall to figure out.

[sam.mckay]

Ok great stuff. Sam

[hunghahuy]

So, if we use the “Events in Progress” pattern in here, we turn off the relationship between Date and Sales table.

And then, all the core measures now need to use USERELATIONSHIP function. Is it correct?

Please correct me if I understand wrong