cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MJB2017
Frequent Visitor

Creating criteria for DAX formulas

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Creating criteria for DAX formulas

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". Smiley Happy

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

View solution in original post

2 REPLIES 2
Microsoft v-ljerr-msft
Microsoft

Re: Creating criteria for DAX formulas

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". Smiley Happy

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

View solution in original post

MJB2017
Frequent Visitor

Re: Creating criteria for DAX formulas

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors