Forum Discussion
Create a metric that checks if a given code has different statuses
I have a field in the table that has 3 different status values. they are: ACCEPTED, EXPIRED and REFUSED.
I want to check when the same provider whose assistance number has 2 or 3 different statuses, If that happens, I answer yes or no.
Hi Anonymous
You can create a new column with below DAX
Column = IF(CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Provider],'Table'[Assistance number]))>1,"Yes","No")Or create a measure with the same code as above column.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
4 Replies
Hi Anonymous
You can create a new column with below DAX
Column = IF(CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Provider],'Table'[Assistance number]))>1,"Yes","No")Or create a measure with the same code as above column.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.v-jingzhang I use direct query mode, 'CALCULATE' function was not allowed as part of calculated column DAX expressions in DirectQuery models.
The metric worked. Can you help me with the column solution?Hi Anonymous
I'm afraid this is not supported for a calculated column in DirectQuery mode because of the following limitation. You can refer to this document for more details. This is a modeling limitation for DirectQuery.
Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions.
So you can only use the measure solution in this scenario.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
v-jingzhang ok, thank you