Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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
Solved! Go to Solution.
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:
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:
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:
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.
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 :
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.
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
)
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |