Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mina97
Helper III
Helper III

count the number of applications

i have the following data

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

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 

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 

please help and thank you 

1 ACCEPTED SOLUTION
FreemanZ
Super User
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:

FreemanZ_0-1698042055036.png

 

 

View solution in original post

6 REPLIES 6
mina97
Helper III
Helper III

@FreemanZ i love youuuu that helpeddd thank YOU!

FreemanZ
Super User
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:

FreemanZ_0-1698042055036.png

 

 

123abc
Community Champion
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.

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 
Screenshot 2023-10-23 082511.png


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  
123abc
Community Champion
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(
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
)
)

 

  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.

 

 

 

 

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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