topic Re: count the number of applications in DAX Commands and Tips

count the number of applications

mina97 — Mon, 23 Oct 2023 05:00:34 GMT

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

please help and thank you

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

please help and thank you

Re: count the number of applications

123abc — Mon, 23 Oct 2023 05:08:07 GMT

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.

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

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

Re: count the number of applications

mina97 — Mon, 23 Oct 2023 05:27:54 GMT

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(

COUNTROWS('RPT_GHAD_PERFORMANCE'),

FILTER('RPT_GHAD_PERFORMANCE', 'RPT_GHAD_PERFORMANCE'[STATUS_NAME_EN] = "Approved"

&& 'RPT_GHAD_PERFORMANCE'[REQUEST_NUMBER] IN

(FILTER('RPT_GHAD_PERFORMANCE', 'RPT_GHAD_PERFORMANCE'[TASK_TYPE] = "Resubmit"

&& 'RPT_GHAD_PERFORMANCE'[REQUEST_NUMBER] = EARLIER('RPT_GHAD_PERFORMANCE'[REQUEST_NUMBER])))))
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

please help and thank you

Re: count the number of applications

123abc — Mon, 23 Oct 2023 05:43:59 GMT

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

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(
COUNTROWS('RPT_GHAD_PERFORMANCE'),
FILTER(
'RPT_GHAD_PERFORMANCE',
'RPT_GHAD_PERFORMANCE'[STATUS_NAME_EN] = "Approved" &&
'RPT_GHAD_PERFORMANCE'[REQUEST_NUMBER] IN
(
CALCULATETABLE(
'RPT_GHAD_PERFORMANCE',
'RPT_GHAD_PERFORMANCE'[TASK_TYPE] = "Resubmit"
)
)
)
)

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
)
)

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.

Re: count the number of applications

mina97 — Mon, 23 Oct 2023 05:52:24 GMT

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
)
)

Re: count the number of applications

FreemanZ — Mon, 23 Oct 2023 06:21:08 GMT

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:

Re: count the number of applications

mina97 — Tue, 24 Oct 2023 05:42:07 GMT

FreemanZ i love youuuu that helpeddd thank YOU!