Forum Discussion

Francoisvdm's avatar
Francoisvdm
New Member
4 years ago
Solved

Calculations on a row returned by Distinct Count

Hoping there is a simple answer on this, but I couldn't find anything in the forums and been scratching my head on this one:

 

I have a matrix visual with the row field = 'Data'[Test Sceanrio] and Values = Count of 'Data'[Test Case Description] - this uses Count(Distinct)
The results display correctly, but on certain rows that are returned I want to perform calculations on the result for that row e.g. if value = xxx reduce result by 5, else return the unadjusted  result

 

The following DAX query returns the correct values per row, but I want to adjust the result for a specific test case description value. 

e.g. IF (Test Case Description = abc, Volume Tested +5, Volume Tested) but I can't include the IF statement correctly in the measure.

 

Volume Tested = CALCULATE(
DISTINCTCOUNTNOBLANK(Data[Test Case Description]),
'Data'[Overall Status Description] in {"OK","Errors","In Process"}
) + 0
 
Any help greatly appreacited.
 

 

 

  • Francoisvdm's avatar
    Francoisvdm
    4 years ago

    Thanks again - got this syntactically correct now, but  it did not quite do what I wanted. Did a bit more digging and finally came up with the following which works fine.

     

    Volume Tested  =
    var test = CALCULATE(
    DISTINCTCOUNTNOBLANK(Data[Test Case Description]),
    'Data'[Overall Status Description] in {"OK","Errors","In Process"}
    ) + 0
    var check1 = CONTAINS('Data',Data[Test Scenario],"Customer Survey")
    var check2 = CONTAINS('Data',Data[Test Scenario],"Customer Ad-hoc")
    return
    IF(check1 = true(),test - 9,IF(check2 = true(),test - 3,test))
     
     
    appreciate the help, it got me on the right path.
     

5 Replies

  • Francoisvdm's avatar
    Francoisvdm
    New Member
    4 years ago

    Thanks again - got this syntactically correct now, but  it did not quite do what I wanted. Did a bit more digging and finally came up with the following which works fine.

     

    Volume Tested  =
    var test = CALCULATE(
    DISTINCTCOUNTNOBLANK(Data[Test Case Description]),
    'Data'[Overall Status Description] in {"OK","Errors","In Process"}
    ) + 0
    var check1 = CONTAINS('Data',Data[Test Scenario],"Customer Survey")
    var check2 = CONTAINS('Data',Data[Test Scenario],"Customer Ad-hoc")
    return
    IF(check1 = true(),test - 9,IF(check2 = true(),test - 3,test))
     
     
    appreciate the help, it got me on the right path.
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      No problem! Glad to be helpful 😊

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    You can use a variable. For eg.

     

    Volume Tested = 
var test = CALCULATE(
           DISTINCTCOUNTNOBLANK(Data[Test Case Description]),
           'Data'[Overall Status Description] in {"OK","Errors","In Process"}
           ) + 0
Return
IF(
   test = 50 //(say),
   test - 5,
   test
)

     




    • Francoisvdm's avatar
      Francoisvdm
      New Member
      4 years ago

      Hi Aditya,

       

      Thanks for the quick reply.

      Not sure if I am missing something and may have not been that clear in my explanation

       

      I want to test a different field value and decide the calculation based on its value.

      I tried the following but get an invalid token syntx error, line 8 offset 1 which is the table field Data[Test Case Description]

       

       
      Volume Tested =
      var test = CALCULATE(
      DISTINCTCOUNTNOBLANK(Data[Test Case Description]),
      'Data'[Overall Status Description] in {"OK","Errors","In Process"}
      ) + 0
      Return
      IF(
      'Data[Test Case Description] = "Customer Survey",
      test - 5,
      test
      )

       

      If I try to move the IF clause before the Return  it flags an invalid token at line 6 offset 19  which is the condition of the IF statement 
       

      Volume Tested2 =
      var mycount1 = CALCULATE(
      DISTINCTCOUNTNOBLANK(Data[Test Case Description]),
      'Data'[Overall Status Description] in {"OK","Errors","In Process"}
      ) + 0
      var mycount2 = IF('Data[Test Case Description] = "Customer Survey",
      mycount1 - 5,
      mycount1
      )
      return
      CALCULATE(mycount2)
       
      Tried a bunch of variations on this but I am obviously  missing something fundamental. 
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        These are the syntax errors I see which correspond to the line numbers you mentioned

         

         

        Use 'Data' instead of 'Data

         

        Regards