Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Counts based on two columns

Hi All,

 

I am new to Power BI.  I am hoping someone can help. 

 

I am tasked to count the number of Customer Orders (named 'CO Header' in my screenshot) that "Meets SLA" versus "Does Not Meet SLA".   Our orders can have one line or multiple lines associated with them(the lines column is named CO Line in my screenshot). 

 

To qualify as "Meets SLA", all of the lines must equal "Meets SLA".  If one line is listed as "Does Not Meet SLA" then the entire order is counted as "Does Not Meet SLA".

 

These totals will eventually be put into a pie chart to show the count of "Meets SLA" versus "Does Not Meet SLA".

 

CP00000021 would count as Does Not Meet SLA (due to lines #6 and #7)

CP00000022 would count as Meets SLA

CP00000023 would count as Meets SLA

CP00000024 would count as Does Not Meet SLA (due to line #1)

CP00000025 would count as Meets SLA

CP00000026 would count as Does not Meet SLA

I hope this isn't confusing.  Let me know if you have any questions.  Thanks for reading my post.

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Anonymous,

     

    Try these measures:

     

    Meets SLA = 
SUMX (
    VALUES ( Table1[CO Header] ),
    VAR vHeader = Table1[CO Header]
    VAR vCount =
        COUNTROWS (
            FILTER (
                Table1,
                Table1[CO Header] = vHeader
                    && Table1[SLA] = "Does Not Meet SLA"
            )
        )
    RETURN
        IF ( ISBLANK ( vCount ), 1 )
)
    Does Not Meet SLA = 
SUMX (
    VALUES ( Table1[CO Header] ),
    VAR vHeader = Table1[CO Header]
    VAR vCount =
        COUNTROWS (
            FILTER (
                Table1,
                Table1[CO Header] = vHeader
                    && Table1[SLA] = "Does Not Meet SLA"
            )
        )
    RETURN
        IF ( NOT ISBLANK ( vCount ), 1 )
)

     

    I added a row to the sample data so each measure returns a different result.

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Anonymous,

     

    Try these measures:

     

    Meets SLA = 
SUMX (
    VALUES ( Table1[CO Header] ),
    VAR vHeader = Table1[CO Header]
    VAR vCount =
        COUNTROWS (
            FILTER (
                Table1,
                Table1[CO Header] = vHeader
                    && Table1[SLA] = "Does Not Meet SLA"
            )
        )
    RETURN
        IF ( ISBLANK ( vCount ), 1 )
)
    Does Not Meet SLA = 
SUMX (
    VALUES ( Table1[CO Header] ),
    VAR vHeader = Table1[CO Header]
    VAR vCount =
        COUNTROWS (
            FILTER (
                Table1,
                Table1[CO Header] = vHeader
                    && Table1[SLA] = "Does Not Meet SLA"
            )
        )
    RETURN
        IF ( NOT ISBLANK ( vCount ), 1 )
)

     

    I added a row to the sample data so each measure returns a different result.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    DataInsights 

     

    Thanks so much!!!!  This worked perfectly!   ğŸ™‚