Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |