Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I'm new to BI, and would really appreciate your input on what would be the next steps to take for a report I'm working on. Currently, I've a list of projects (PO's), with several criteria that need to be met before they are completed. Once all criteria are met, the PO in general is completed.
I'm trying to create a measure, which will allow me to use Count Distinct, to measure the number of PO's Completed vs. the number of PO's Outstanding.
I first need to create a measure that will count the PO's that are completed, but only if all line items are complete.
I need to create a second measure then to count all the PO's that are not completed.
To give you an idea of the table:
Column 1 Column 2 Column 3 Column 4
Department PO Number Line Item PO Status
Photo Gallery 58286613 10 Not Complete
Photo Gallery 58286613 20 Complete
So far I've a measure looking a little like this:
Measure 2 = CALCULATE(
SUM('Data List'[PO Number],
FILTER(
'Data List',
'Data List'[PO Number]="Completed",
'Data List'[Line Item]="Completed"
I'm trying to find a way to finish the formula so that the result excludes at PO's which still contain an "incomplete", but I'm not savvy enough with DAX yet, and various websites I've checked out aren't offering anything similar to this particular quandry.
Really appreciate input on this!
Cheers!
Solved! Go to Solution.
Hi @MJB2017,
Based on my test, you should be able to use the formula below to get the distinct count of PO which excludes at PO's that still contains an "incomplete".
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( 'Data List', 'Data List'[PO Number], "IsCompleted", CALCULATE ( DISTINCTCOUNT ( 'Data List'[PO Number] ), FILTER ( 'Data List', NOT CONTAINS ( 'Data List', 'Data List'[PO Status], "Not Complete" ) ) ) ), [IsCompleted] = 1 ) )
Regards
Hi @MJB2017,
Based on my test, you should be able to use the formula below to get the distinct count of PO which excludes at PO's that still contains an "incomplete".
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( 'Data List', 'Data List'[PO Number], "IsCompleted", CALCULATE ( DISTINCTCOUNT ( 'Data List'[PO Number] ), FILTER ( 'Data List', NOT CONTAINS ( 'Data List', 'Data List'[PO Status], "Not Complete" ) ) ) ), [IsCompleted] = 1 ) )
Regards
Hi @v-ljerr-msft,
Thank you so much! I'm going through the measure now to better understand the DAX model, but having tested it out it works beautifully.
Really appreciate your help on this!
Best Regards,
M
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |