topic Re: Calculations on a row returned by Distinct Count in DAX Commands and Tips

Calculations on a row returned by Distinct Count

Francoisvdm — Fri, 25 Feb 2022 09:30:13 GMT

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.

Re: Calculations on a row returned by Distinct Count

Anonymous — Fri, 25 Feb 2022 09:58:16 GMT

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 )

Re: Calculations on a row returned by Distinct Count

Francoisvdm — Fri, 25 Feb 2022 11:17:45 GMT

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.

Re: Calculations on a row returned by Distinct Count

Anonymous — Fri, 25 Feb 2022 11:31:31 GMT

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

Use 'Data' instead of 'Data

Regards

Re: Calculations on a row returned by Distinct Count

Francoisvdm — Fri, 25 Feb 2022 15:00:09 GMT

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.

Re: Calculations on a row returned by Distinct Count

Anonymous — Mon, 28 Feb 2022 05:21:28 GMT

No problem! Glad to be helpful 😊