Create a metric that checks if a given code has different statuses

[edumach]

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.

[edumach]

@v-jingzhang ok, thank you

[edumach]

@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?

[v-jingzhang]

Hi @edumach 

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 · Accepted solution]

Hi @edumach 

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.