Forum Discussion

HankScorpio2's avatar
HankScorpio2
Icon for Helper I rankHelper I
1 year ago
Solved

Set a measure output for each agent using if

I have a table of data. Each ID relates to an agents score in that section. 

 

What I am trying to do is sum up all the sections for a result for the agent. However, if as in below an agent receives a 0 in 7.1, 7.2, 7.3. I want to set the score for just that ID to 0. Overiding the SUM of the rest of the agents scores.

 

The relevant cell highlighted below.

I tried using this if statement however, its sets the results of everyone to 0. Not just the agent on that ID.

How can I affect just the sum of the ID involved.

This is my current code:

FinalScore = IF(CALCULATE(SUM('Quality Data'[Result]),Filter('Quality Data',SUM('Quality Data'[Section]) = 7.1))= 0 ||
CALCULATE(SUM('Quality Data'[Result]),Filter('Quality Data',SUM('Quality Data'[Section]) = 7.2))= 0 ||
CALCULATE(SUM('Quality Data'[Result]),Filter('Quality Data',SUM('Quality Data'[Section]) = 7.3))= 0,
SUM('Quality Data'[Result])*0,SUM('Quality Data'[Result]))

 

This is my model.

 

 

 

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    1 year ago

    I think the problem you are running into is, if there are no 7.1 rows for an ID, the SUM of the 7.1 rows for that ID is 0.  You need to check for there being rows and for the sum of the rows to be 0, someting like this.

    Measure = 
VAR _71 = CALCULATE ( COUNTROWS ( 'Quality Data' ), 'Quality Data'[Section] = 7.1, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _72 = CALCULATE ( COUNTROWS ( 'Quality Data' ), 'Quality Data'[Section] = 7.2, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _73 = CALCULATE ( COUNTROWS ( 'Quality Data' ), 'Quality Data'[Section] = 7.3, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _71s = CALCULATE ( SUM ( 'Quality Data'[Result] ), 'Quality Data'[Section] = 7.1, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _72s = CALCULATE ( SUM ( 'Quality Data'[Result] ), 'Quality Data'[Section] = 7.2, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _73s = CALCULATE ( SUM ( 'Quality Data'[Result] ), 'Quality Data'[Section] = 7.3, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
RETURN
SWITCH (
    TRUE(),
    AND(_71 > 0, _71s = 0), 0,
    AND(_72 > 0, _72s = 0), 0,
    AND(_73 > 0, _73s = 0), 0,
    SUM('Quality Data'[Result])
)

     

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    1 year ago

    I think the problem you are running into is, if there are no 7.1 rows for an ID, the SUM of the 7.1 rows for that ID is 0.  You need to check for there being rows and for the sum of the rows to be 0, someting like this.

    Measure = 
VAR _71 = CALCULATE ( COUNTROWS ( 'Quality Data' ), 'Quality Data'[Section] = 7.1, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _72 = CALCULATE ( COUNTROWS ( 'Quality Data' ), 'Quality Data'[Section] = 7.2, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _73 = CALCULATE ( COUNTROWS ( 'Quality Data' ), 'Quality Data'[Section] = 7.3, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _71s = CALCULATE ( SUM ( 'Quality Data'[Result] ), 'Quality Data'[Section] = 7.1, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _72s = CALCULATE ( SUM ( 'Quality Data'[Result] ), 'Quality Data'[Section] = 7.2, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
VAR _73s = CALCULATE ( SUM ( 'Quality Data'[Result] ), 'Quality Data'[Section] = 7.3, ALLEXCEPT ( 'Quality Data', 'Quality Data'[ID] ) )
RETURN
SWITCH (
    TRUE(),
    AND(_71 > 0, _71s = 0), 0,
    AND(_72 > 0, _72s = 0), 0,
    AND(_73 > 0, _73s = 0), 0,
    SUM('Quality Data'[Result])
)

     

    • HankScorpio2's avatar
      HankScorpio2
      Icon for Helper I rankHelper I
      1 year ago

      jdbuchanan71 I agree in the transform I did there are 3 possible outcomes. Yes, No and N/a. I set Yes to be 1, No to be 0 and N/a to be null. 

       

      So you are saying that if the value is null the sum of Null is 0. Therefore even with your solution all results become 0. 

       

      I will have to consider updating the transform. Maybe Yes 1, No -1 and N/a 0. Then I could check -1 which should be less problematic

       

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    1 year ago

    No, with my solution, it checks if the count of rows is > 0 and the sum is 0 so it verifies that 

    a. there are 7.1 rows and 

    b. the sum of the 7.1 rows is 0

    It does this for 7.1, 7.2 and 7.3

    In the screen shot, ID 1 has an amonut because it has 7.1 and 7.2 rows but the sum of those rows is not 0

    If and ID has no 7.1, 7.2 or 7.3 rows or all of thier  7.1, 7.2 or 7.3 rows <> 0, it will give the sum.

    I have attached my sample file for you to look at.

     

  • quantumudit's avatar
    quantumudit
    Icon for Super User rankSuper User
    1 year ago

    Hello HankScorpio2 

    While a sample dataset and expected results screenshot would have been ideal, I've created a sample dataset based on your provided screenshot and developed the following DAX measure to achieve the desired outcome:

    FinalScore = 
VAR _id =
    SELECTEDVALUE ( 'Quality Data'[ID] )
VAR _filteredTbl =
    FILTER ( 'Quality Data', 'Quality Data'[ID] = _id )
VAR _flaggedSectionCount =
    COUNTROWS (
        FILTER (
            'Quality Data',
            'Quality Data'[ID] = _id
                && 'Quality Data'[Section] IN { 7.1, 7.2, 7.3 }
        )
    )
RETURN
    IF ( _flaggedSectionCount > 0, 0, SUMX ( _filteredTbl, 'Quality Data'[Result] ) )

     

    Based on the data visible in the provided screenshot, the result is as follows:

     

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

     

    Proud to be a Super User