Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppgandhi11
Helper V
Helper V

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
v-cherch-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.