cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ppgandhi11 Member
Member

DAX calculation help - list of values

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

1 REPLY 1
Community Support Team
Community Support Team

Re: DAX calculation help - list of values

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.