cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## count the number of applications

i have the following data

 order stauts 216 submited 216 accepted 217 submited 217 resumited 217 accepted 218 submit 218 return to applicant 218 resubmit 218 reject

1- i need to count the number of orders that got accepted without being re submit so in this case it should give one order

2-

1- i need to count the number of orders that got accepted with being re submit so in this case it should give one order

1 ACCEPTED SOLUTION
Super User

hi @mina97 ,

not sure if i fully get you. you may try to write two measures like:

``````AcceptedNotResubmited =
SUMX(
VALUES(data[order]),
VAR _list = CALCULATETABLE( VALUES(data[status]))
RETURN
IF(
"Accepted" IN _list && NOT "resubmited" IN _list,
1,
0
)
)``````
``````AcceptedResubmited =
SUMX(
VALUES(data[order]),
VAR _list = CALCULATETABLE( VALUES(data[status]))
RETURN
IF(
"Accepted" IN _list && "resubmited" IN _list,
1,
0
)
)``````

they worked like:

6 REPLIES 6
Helper III

@FreemanZ i love youuuu that helpeddd thank YOU!

Super User

hi @mina97 ,

not sure if i fully get you. you may try to write two measures like:

``````AcceptedNotResubmited =
SUMX(
VALUES(data[order]),
VAR _list = CALCULATETABLE( VALUES(data[status]))
RETURN
IF(
"Accepted" IN _list && NOT "resubmited" IN _list,
1,
0
)
)``````
``````AcceptedResubmited =
SUMX(
VALUES(data[order]),
VAR _list = CALCULATETABLE( VALUES(data[status]))
RETURN
IF(
"Accepted" IN _list && "resubmited" IN _list,
1,
0
)
)``````

they worked like:

Community Champion

To count the number of orders that got accepted without being resubmitted in Power BI, you can create a calculated column or measure based on your data. Here are the steps for both scenarios:

Scenario 1: Count the number of orders that got accepted without being resubmitted.

1. Go to the "Model" view in Power BI to create a calculated column:

• Click on the "Data" icon on the left pane.
• Select the table containing your data (e.g., "Orders").
• Click on "New Column" in the modeling tab.
2. In the formula bar, you can use a DAX (Data Analysis Expressions) formula to create the calculated column. You can use the following formula:

AcceptedWithoutResubmit = IF(MAX('Orders'[status]) = "accepted" && COUNTROWS(FILTER('Orders', 'Orders'[order] = EARLIER('Orders'[order]) && 'Orders'[status] = "resubmit")) = 0, 1, 0)

1. This formula checks if the order status is "accepted" and if there are no corresponding "resubmit" entries for the same order. It assigns a 1 if the condition is met, and 0 otherwise.

2. After creating the calculated column, you can use it in your visualizations to count the number of orders that meet the criteria.

Scenario 2: Count the number of orders that got accepted with being resubmitted.

To count the number of orders that got accepted with being resubmitted, you can use a similar approach. The DAX formula would be:

AcceptedWithResubmit =
CALCULATE(
COUNTROWS('Orders'),
FILTER('Orders', 'Orders'[status] = "accepted" && 'Orders'[order] IN
(FILTER('Orders', 'Orders'[status] = "resubmit" && 'Orders'[order] = EARLIER('Orders'[order]))))

This formula calculates the count of orders where the status is "accepted" and the order appears in the list of orders with a "resubmit" status.

After creating the calculated column for scenario 2, you can also use it in your visualizations to count the number of orders that meet this criteria.

Remember to replace 'Orders' with the actual name of your table containing the data, and adjust the column names as necessary based on your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helper III

Hey i really appreciate your help
First i am dealing with a data base so i can not add a new column it has to be a meausre
i tried this Dax code :

AcceptedWithResubmit =
CALCULATE(
and it gives me this error

2-
i also tried this one
AcceptedWithoutResubmit = IF(MAX('Orders'[status]) = "accepted" && COUNTROWS(FILTER('Orders', 'Orders'[order] = EARLIER('Orders'[order]) && 'Orders'[status] = "resubmit")) = 0, 1, 0)
bit it did not work because of the bold word it did not show

Community Champion

It seems that you're encountering issues with your DAX measures in Power BI. Let's address each of your questions:

1. AcceptedWithResubmit Measure:

The DAX code you provided seems to be on the right track. It's attempting to count the number of orders that are "Approved" and have a corresponding "Resubmit" task. However, the error you're seeing may be related to the data structure and context in your model.

To make this measure work, you should ensure that your data model is set up correctly. Specifically, you should check the relationships between tables, especially if you have a table containing request numbers and another table containing the status and task type information. Make sure you have a relationship established between them. If there's no direct relationship, you might need to use functions like RELATED or RELATEDTABLE to create the necessary filter context.

Here's the modified measure:

AcceptedWithResubmit =
CALCULATE(
FILTER(
(
CALCULATETABLE(
)
)
)
)

AcceptedWithoutResubmit Measure:

Your code for this measure seems to be almost correct, but the issue might be with the context transition of EARLIER. To address this, you should wrap your condition with SUMX to create a proper iterator:

AcceptedWithoutResubmit =
SUMX(
FILTER('Orders', 'Orders'[status] = "accepted"),
IF(
COUNTROWS(
FILTER(
'Orders',
'Orders'[order] = EARLIER('Orders'[order]) && 'Orders'[status] = "resubmit"
)
) = 0,
1,
0
)
)

1. The SUMX function allows you to aggregate the results properly and should work in the context of a measure.

Make sure that your data model is correctly set up, and these DAX measures should help you achieve your desired results in Power BI. If you continue to encounter issues, please double-check your data model and relationships between tables.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helper III

Hey so this one worked perfectly! but i need it to count the unique order number please help
AcceptedWithoutResubmit =
SUMX(
FILTER('Orders', 'Orders'[status] = "accepted"),
IF(
COUNTROWS(
FILTER(
'Orders',
'Orders'[order] = EARLIER('Orders'[order]) && 'Orders'[status] = "resubmit"
)
) = 0,
1,
0
)
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors