Sorry for the long question.

In PowerBI I have fields called 'user ID' and 'service'. Each row represents an activity, so may not be unique. I want to create a new variable called '5+ flag' that for each ID, counts how many times it appears and then assigns 1 if it five or more, and 0 otherwise.

Eventually I would like to use this variable in a calculated measure that counts the number of rows with a '5+ contacts' = 1 and certain value for 'service' and divides it by the number of unique IDs with '5+ contacts' = 1. The DAX code for this measure is below:

measure_1 = DIVIDE( COUNTROWS( FILTER( 'data', 'data'[5+ flag] = 1 && 'data'[service] = "Design" ) ), CALCULATE( DISTINCTCOUNT( 'data'[userID]), 'data'[5+ flag] = 1 ) )

Unfortunately, when I tried making '5+ flag' using a calculated column (using DAX code below), I got the incorrect values. Although measure_1 did work. I believe this has something to do with calculated columns being fixed and not dynamic.

5+ flag = IF( CALCULATE( COUNTROWS('Merged data'), FILTER( 'Merged data', 'Merged data'[patient ID] = EARLIER('Merged data'[patient ID]) && 'Merged data'[stage of care] = "05 Chemotherapy" ) ) >= 5, 1, 0 )

When I tried making it using a calculated measure (using DAX code below), I got the correct numbers, but then measure_1 gave this error: 'A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'

5+ contacts = SUMX( VALUES('Merged data'[Patient ID]), CALCULATE(COUNT('Merged data'[Patient ID])) )

Could anyone tell me how to either get the correct numbers for '5+ contacts' as a calculated column, or how to get measure_1 working when '5+ contacts' is a calculated measure. Any advice would be really appreciated.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.