DAX calculation help - list of values

[ppgandhi11]

Hi All,

I have this working formula.  What this does is:

returns # of rows from Query1, where the Service Code is same value and Insurer is same value and NPI is not the same.

This works perfectly fine when I have only single value returned for a given Service code and single value returned for Insurer. Now I have a case where I may get list of values for Insurer (based on Provider NPI value). This formula breaks there since it gets the highest of those values only. If the given Provider NPI is associated with let say Insurer A, B and C then the bold highlighted part must be changed to accomodate that. How can I get this done?

I believe DAX has a IN{...} functionality so it must be changed to:  .. Query1[Insurer] IN{...} && ..

I am having hard time deriving IN clause. I am trying to create a table value that shows:

Provider NPI   Insurer

X                        A

X                        B

X                        C

so that I can use Insurer field above in the IN clause. Can someone please help? much appreciated. Thanks.

VAR Ins_Spec_Num =
    CALCULATE(
        COUNTROWS(Query1),
        FILTER(
            ALL(Query1),
            Query1[Service Code] = MAX(Query1[Service Code]) &&
            Query1[Insurer] = MAX(Query1[Insurer]) &&
            Query1[Provider NPI] <> MAX(Query1[Provider NPI])
            )
            )
return Ins_Spec_Num

[v-cherch-msft]

Hi @ppgandhi11

You may have a look at below article. If you need further help, please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.

https://www.sqlbi.com/articles/the-in-operator-in-dax/

Regards,

Cherie