Using IN Function for a list produces same calculated value

[ce138867]

I've created a measure with two variables but the returned value = 100%, when it should equal 31% where VAR IP = 4 and VAR IPED = 13.  I created separate measures for each VAR to check the calculation and noticed that in VAR IPED, when I use the IN function with multiple values, it still gives me the same calculation as VAR IP where am only filtering on one value.  Should I be using a different function?

measure =

VAR IP = 

CALCULATE(
DISTINCTCOUNT('Baseline'[ID]),FILTER('Baseline',
AND([Flag]= "1",Baseline[Type] IN {"IP"}))
)
VAR IPED =
CALCULATE
(DISTINCTCOUNT('Baseline'[ID]),
FILTER('Baseline',AND ([Flag]= "1",
Baseline[Type] IN {"IP", "NonIP"})   ;;;;;;;;;;;;Looking at multiple values using IN still gives me same number as VAR IP
)
)

return

Divide(IP, IPED)

[PaulDBrown]

@ce138867 

Does the column Baseline[Type] contain values other than "IP or "NonIP" ?(I would assume so becuase if not you can exclude the IN filter expression from the VARIPED)

You could try this for VARIPED

VAR IPED =
CALCULATE
(DISTINCTCOUNT('Baseline'[ID]),
FILTER('Baseline', [Flag]= "1",
Baseline[Type] IN {"IP", "NonIP"}) 

[PaulDBrown]

VAR IPED =
CALCULATE
(DISTINCTCOUNT('Baseline'[ID]),
FILTER('Baseline', [Flag]= "1",
Baseline[Type] IN {"IP", "NonIP"}) 

[ce138867]

Yes, Baseline[Type] contains other values other than "IP" and "NonIP".  Taking the AND out gives me an "Unexpected tuple" error and I need both filters to be true. 

[v-yiruan-msft]

Hi @ce138867 ,

Is there any data which type is NonIP and Flag is 1 in the table Baseline? Could you please provide some sample data of table Baseline?

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[ce138867 · Accepted solution]

Yes, there are rows where Type = NonIP and Flag = 1. I have de-identified the data based on HIPAA.  What I found when playing with this more is that I had a Type filter set for the page and my result was being filtered based on my selection. When I removed the page filter the correct percentage was being displayed so my original formula was working the way I wanted. Thanks to everyone that helped me troubleshoot.

ID Type Flag
1 NonIP 1
2 IP 1
3 NonIP 1
4 NonIP 1
5 IP 1
6 NonIP 1
7 NonIP 1
8 NonIP 1
9 NonIP 1
10 NonIP 1
11 NonIP 1
12 IP 1
13 IP 1